compare two lists in Excel, howto ?

[hellbeach]

Hello everyone
Does anyone know a way to compare two lists with a common columb and get one list with the result, like this:

list 1:
columb1 columb2
1 hello
2 hello2
1 hello3

list 2:
columb1 columb2
1 hello4
1 hello5
2 hello6

and the resulting list will be:
columb1 columb2
2 hello2
2 hello6


/Dan

 

[xlbo]

surely to get that, you just need to copy the data from 2 underneath the data from 1 and sort......or am I missing something ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[hellbeach]

well what if I have two list with 5000 entries and I need to merge the two list into one, getting all the data on one row. If I understand you correctly I will get the data on two separate lines if I do it the way you propose, or am I missing something ?

 

[xlbo]

well - that's exactly what you showed on your example:

and the resulting list will be:
columb1 columb2
2 hello2
2 hello6

Please provide clarification as to what exactly you need to do ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[bigbull15]

I, myself am looking for a solution to what I imagine is a similar question.
The following example represents the information I am trying to match.

A B E F
1 PARTNO RETAIL Item list_price
2 W008570 $12.41 VR8200A2124 $0.00
3 VX4 $739.02 CR41KQPFV980WB $0.00
4 VS210 $63.29 ACG24243F $0.00
5 VRK1200 $1,021.63 ACG20253F $0.00
6 VR8300A4508 $162.56 ACG16253F $0.00
7 VR8200A2124 $138.11 ACG14253F $0.00
8 VP600B $367.96 99084 $0.00
9 VP5 $380.92 96839 $0.00


I need to match each "Item Code" in Column (E) with it's corresponding "PartNo" in Column (A) and return the value of Col.(B) for that Row. For example, Item "VR8200A2124" in cell (E2) matches cell (A7). I need Excel to return the Value of (B7) to (F2). I am not able to simply drag and drop this information for Col.(E) contians 453 items to cross with the 35,682 items in Col.(A).

If this is not similar to the original question I do apologize, none the less, any help would be appreciated.

 

[KenWright]

Bigbull, in F2 put the following and copy down:-

=VLOOKUP(C2,$A$2:$B$9,2,0)

If you don't want any errors where it can't find a match, use:-

=IF(ISNA(VLOOKUP(C2,$A$2:$B$9,2,0)),"",VLOOKUP(C2,$A$2:$B$9,2,0))

HellBeach - Have you considered a Pivot table?? It may well do what you want very easily, though the columns would be reversed with your lable sto the left and the counts to the right. For more info on pivots:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Regards
Ken..........

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[bigbull15]

Ken, I appreciate your efforts, but that didn't work. Excel returned #N/A. I,m not sure what "C2" in your formula is supposed to represent? Column (C) was blank, but even after deleting the blank columns which put the "Item Code" info in Column (C), the formula still returned #N/A. Any advice?

 

[Zig1]

In Column F, starting in row 2 try: =VLOOKUP(E2,$A$2:$B$9,2,False)

 

[bigbull15]

Zig,

I did as you said and still nothing. I swear I'm about to pull my freakin' hair out over this thing. It should be simple. Both column (A) and (E) are sorted ascending, aligned vertically to the left, with the same font type and size. Does any of this affect (effect) the way the formula works?

There is one thing I was wondering which I haven't seen any info. on, thats the maximum number of rows in an array that can be referenced in VLOOKUP?

 

[Zig1]

Bigbull,

I tried, using your data and it worked.

The only thing that I can think of is to check the length of the fields in Columns A and E with the =Len(Column,Row) function. If they are different lengths, if E is longer, then try:
=VLOOKUP(Trim(E2),$A$2:$B$9,2,False)

If that doesn't work, you may have to Trim the values in the portion of the table in Column A. Remember that for the VLOOKUP to work the value being queried must be in the first column.

Regarding any maximum length for Vlookup, to the best of my knowledge it would be 65,536. The larger the Vlooup, the longer it will take the calculation to run.

Hope this helps.

 

[BenRowe]

KenWright's formula worked for me. Why don't you post your formula?

 

[hellbeach]

Thanks for the help everyone, VLOOKUP was exactly the thing I was looking for. Got it working after some tweaking.

/Hellbeach

 

[bigbull15]

I would like to thank everyone myself. Your formulas work wonderfully. I came to find out my problem is with the code excel is using to sort my item list in Column A.

That's a whole different issue though.

 

[KenWright]

Apologies, I put a C instead of an E in the formula, as was pointed out. Saw 3 columns and assumed A,B,C

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------