Look up duplicate amounts with unique batch numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JHL - 06 May 2008 20:34 GMT

I have two lists in separate spreadsheets, illustrated below. How do I find
a match using the Amount1 field as the key, and match to the second list with
the correct batch number? Both lists are unequal in the number of records.

Thanks for any help.

Amount    Batch Number            Amount1 (key)
3.45     8123953001            3.45
3.88     8123971001            3.88
4.40     8123872001            4.40
4.43     8123A42001            4.43
4.45     8123970001            4.45
5.03     8123681001            5.03
5.15     8123839001            5.15
5.49     8123571001            5.49
5.49     8123803001            5.49
5.49     8123804001            5.49
5.49     8123983001            5.49
5.49     8123A52001            5.49

Reply to this Message

Mike H - 06 May 2008 20:42 GMT

assuming your data are in Sheet 1 and the 'Amount 1 (key' value are in A2
down on another sheet put this in b2 and drag down

VLOOKUP(A2,Sheet1!$A$2:$B$13,2,FALSE)

Mike

> I have two lists in separate spreadsheets, illustrated below. How do I find
> a match using the Amount1 field as the key, and match to the second list with
[quoted text clipped - 15 lines]
> 5.49 8123983001 5.49
> 5.49 8123A52001 5.49

Reply to this Message

JHL - 06 May 2008 21:25 GMT

Mike H, thanks for the reply. However, I had already tried this. The
problem is it doesn't recognize the other batches with the duplicate amount
for 5.49. Vlookup will only match the first batch and repeat that reference
for the other 5.49 items.

> assuming your data are in Sheet 1 and the 'Amount 1 (key' value are in A2
> down on another sheet put this in b2 and drag down
[quoted text clipped - 22 lines]
> > 5.49 8123983001 5.49
> > 5.49 8123A52001 5.49

Reply to this Message

Dave - 06 May 2008 23:14 GMT

Hi,
The Lookup value has to be from a list of unique data.
When your lookup value is 5.49, which batch number do you actually want
returned?
Also, something I didn't understand from your original post, You said you
had 2 lists in separate spreadsheets, but your data is arranged in 3 lists
(Amount, Batch Number, Amount 1)
Regards - Dave.

Reply to this Message

JHL - 07 May 2008 01:23 GMT

> Hi,
> The Lookup value has to be from a list of unique data.
[quoted text clipped - 4 lines]
> (Amount, Batch Number, Amount 1)
> Regards - Dave.

Dave
If possible I would like all amounts (including any duplicates) to be
matched with the batch number found in the 2nd list. For example in the
list with just the amounts, if the single amount list has 3 amounts for
5.49, the second list will most likely have 3 amounts for 5.49 WITH a
unique batch no. I want the 3 unique batch numbers returned in the match.

The lists are in two separate files. The amount & batch is one list.
The singular amount is in another list in a different file.

I hope this helps.

Reply to this Message