Formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I need help creating a formula that would do the following: I have two
columns of numbers - they are customer #s. Some of the numbers are in both
columns, others are in one column. How would I set up a formula to identify
which numbers are in one coumns but not in the other? Thanks a lot.
 
I'm not sure how you'd like to show the fact that a number is in one column
vs. both but one way would be putting the information in an adjacent column.
Say you have your data in the first 10 rows of columns A and B. Then in C1
you could put:

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$10,1,0)),"1st One Column","1st Both Columns")
& ", " & IF(ISERROR(VLOOKUP(B1,$A$1:$A$10,1,0)),"2nd One Column","2nd Both
Columns")

This gives you the information but it isn't very friendly. Alternatively
you could use conditional formatting. For example, the conditional
formatting formula for cell A1 would look something like:

=ISERROR(VLOOKUP(A1,$B$1:$B$10,1,0))

Setting the format for when this condition is met to red font would then
cause A1's font to be red (not meeting the condition would yield the default
color - e.g. black). Copy the format to the other cells in column A and then
do similar for column B (=ISERROR(VLOOKUP(B1,$A$1:$A$10,1,0))) and you should
be all set.

Will
 
I had a similar problem and i did the following
1. Copy all data from Col A and paste into a new Col C
2. Copy all data from Col B and paste it Col C. (Below the Last used cell)
by doing this you will have all the Nos in one Col . i.e. Col C

3.Now sort the Col C use ascending
by doing this all the repeating Nos will be seen just below each other

4. in Col D. Type Formula =C1-C2
Copy - Drag this Formula to All Cells in Col D

5. Finally Use a Filter for Col D - select 0 (Zero)
This will only show you row's which has (Zero) in Col D
Delete all these Rows.

Remove the filter and you will have Single Entry of Data in Col C

Long Method but it may help
 
Thanks to all!

claude jerry said:
I had a similar problem and i did the following
1. Copy all data from Col A and paste into a new Col C
2. Copy all data from Col B and paste it Col C. (Below the Last used cell)
by doing this you will have all the Nos in one Col . i.e. Col C

3.Now sort the Col C use ascending
by doing this all the repeating Nos will be seen just below each other

4. in Col D. Type Formula =C1-C2
Copy - Drag this Formula to All Cells in Col D

5. Finally Use a Filter for Col D - select 0 (Zero)
This will only show you row's which has (Zero) in Col D
Delete all these Rows.

Remove the filter and you will have Single Entry of Data in Col C

Long Method but it may help
Click to expand...
 
Back
Top