Data Collation — Find Out the Values in a Column [updated]

**SunOffice** · 08-14-2010, 01:19 AM

Aim: We have a huge list of Deal numbers in a sheet, and some Package IDs in two other different sheets - A & B.
Now we take all of them in a new worksheet, and want to search the Package IDs into the Deals (from Column B to Column A). And this the same should go with Package ID (B) for column 'C'.
Note —› The Package ID can be in the Deals # Column or not. It can be twice or more there.

We would like to get a separate list of the entire package IDs of both the columns (B and C), which are available or not in the Deal Number Column 'A'.

Please look into this matter & help me out, It takes too much time to do Ctrl+F, then note down them one by one. It's a weekly problem.

Thanks in Advance!

Recently I've got some idea...I've used a formula, but don't see any other ray of solution for the next steps.

kindly see the updated attached workbook.

**Vinodsralian** · 08-14-2010, 04:26 AM

I have included two columns to make it easier, hope it helps. I guess you may have to do little modifications as you need!!

**SunOffice** · 08-14-2010, 05:13 AM

WOW... Thanks Vinodsralian!

I've got ur idea, but the same idea I was trying to apply, not worked as sometimes I have confusion abt 'range_lookup' section in the vlookup function.

Now plz see the updated workbook for the next problem and plz tell me:
As we get the same result in the two different columns ('F' and 'G'), can we reflect the same msg ('Not Listed') in the column 'F' to make a common column. (replacing '#N/A' by 'Not Listed' displaying text)

So we can remove the column 'G'.

How we can do the above in the cells where 'Vlookup' is used? Your formula is much easier!

**tigertiger** · 08-14-2010, 05:14 AM

I have already help you for list A, Try do the same for list B

Plz, see 3 NAME defined

**SunOffice** · 08-14-2010, 05:56 AM

Thanks a lot tigertiger!!

your foumale r outstanding, and show ur excellent skills ...WOW!!

Result (A) Available in 'D3':
=IF(n_liA+ROW()-ROW($D$3)+1>COUNTA($B$3:$B$11),"",INDIRECT(("B"&SMALL(list_A,n_liA+ROW()-ROW($D$3)+1))))

Result (A) Not Listed in 'E3':
=IF(ROW()-ROW($D$3)+1>n_liA,"",INDIRECT(("B"&SMALL(NotLi_A,COUNTA($B$3:$B$11)-n_liA+ROW()-ROW($D$3)+1))))

...but if I extend the range in the both formule from COUNTA($B$3:$B$11) to COUNTA($B$3:$B$20), then put a value in the column 'B', it doesn't work.

wht to do if the column 'B' has more values or needs to add more values later??
and wher can I learn abt ur these advance formule like: (n_liA+ROW()-ROW($D$3)

**tigertiger** · 08-15-2010, 06:06 AM

Originally Posted by SunOffice

Thanks a lot tigertiger!!

your foumale r outstanding, and show ur excellent skills ...WOW!!

...but if I extend the range in the both formule from COUNTA($B$3:$B$11) to COUNTA($B$3:$B$20), then put a value in the column 'B', it doesn't work.

wht to do if the column 'B' has more values or needs to add more values later??
and wher can I learn abt ur these advance formule like: (n_liA+ROW()-ROW($D$3)

I think, you should see the 3 NAME (menu: insert / name define..), and you will change the range´s address in these name as well ($B$3:$B$11 -> $B$3:$B$20)

OR other way you make the name for data range, such as define name Data_A then replace all $B$3:$B$11 become Data_A

Or, good way, you define dynamic range by name Data_A

now, I am not at home, so I can help you more detail, I hope I will come back as soon as possible,
Goodluck

**tigertiger** · 08-15-2010, 07:35 AM

Ok, I have already help you for all requirements

now, you can add more and more data for column A, B , C (Deal, Package A, Package B respectively)

Plz, see 9 name in manage name by press Ctrl+F3,

if you have no idea about define name in Excell, plz to read here: http://www.contextures.com/xlnames01.html

gl

**SunOffice** · 08-15-2010, 10:43 PM

tigertiger, Thanks for solving my problem and telling a new advanced way of the right solution.
Vinodsralian, Thanks to you too for ur great help!

It's solved, now what I need to do... = "More Practice"