Lookup with multiple same name entries

[Melvin Purvis]

I have a problem that I've been trying to solve for awhile now, I just can't
figure it out.

Please see towards the bottom for data layout. I am using 2003.

At the very bottom is the yesterdays file info. I get the data from a csv, and
manually enter the ticket #.

Today, I start with a new csv file, shown by todays info.

All I really want to do is to pull the ticket numbers from yesterday into
todays info automatically. This has really stumped me for some reason. Maybe
I'm trying to make it too difficult. All I can really think to do is a vlookup,
but it won't work because of the multiple same name entries.

Can anybody please offer me a suggestion, a formula, something???

Many thanks in advance!!


Todays file
Name Date Qty Ticket#
Adam 12/1 45
Adam 12/7 12
Mike 1/1 34
Tom 1/12 4
Wally 11/5 56


yesterdays file
Name Date Qty Ticket#
Adam 12/1 45 4567
Adam 12/7 12 6787
Betty 12/5 67
Mike 1/1 34 5678

 

[smartin]

I have a problem that I've been trying to solve for awhile now, I just can't
figure it out.

Please see towards the bottom for data layout. I am using 2003.

At the very bottom is the yesterdays file info. I get the data from a csv, and
manually enter the ticket #.

Today, I start with a new csv file, shown by todays info.

All I really want to do is to pull the ticket numbers from yesterday into
todays info automatically. This has really stumped me for some reason. Maybe
I'm trying to make it too difficult. All I can really think to do is a vlookup,
but it won't work because of the multiple same name entries.

Can anybody please offer me a suggestion, a formula, something???

Many thanks in advance!!


Todays file
Name Date Qty Ticket#
Adam 12/1 45
Adam 12/7 12
Mike 1/1 34
Tom 1/12 4
Wally 11/5 56


yesterdays file
Name Date Qty Ticket#
Adam 12/1 45 4567
Adam 12/7 12 6787
Betty 12/5 67
Mike 1/1 34 5678

Hiya,

Take a look at this site that explains a nice way to match multiple
lookup criteria. Let us know if you get stuck!

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Melvin Purvis]

Hiya,

Take a look at this site that explains a nice way to match multiple
lookup criteria. Let us know if you get stuck!

http://xldynamic.com/source/xld.SUMPRODUCT.html

I've looked through the page that you linked to several times now.

I can't see how to apply sumproduct to this problem...

 

[smartin]

I've looked through the page that you linked to several times now.

I can't see how to apply sumproduct to this problem...

Ok no problem. I put your sample data in a worksheet:

Todays file
Name Date Qty Ticket#
Adam 12/1 45
Adam 12/7 12
Mike 1/1 34
Tom 1/12 4
Wally 11/5 56


yesterdays file
Name Date Qty Ticket#
Adam 12/1 45 4567
Adam 12/7 12 6787
Betty 12/5 67
Mike 1/1 34 5678


"Today's file" is in A1 (actual data begins in row 3).
"yesterday's file" is in A10 (actual data begins in row 12).

In D3:
=SUMPRODUCT(--(A3=$A$12:$A$15),--(B3=$B$12:$B$15),($D$12:$D$15))
Then fill down through D7.

Feel free to write back if you would like more explanation, but the site
I referenced does a great job of how and why this works.

By the way, this will only work as advertised if there is only one
ticket per person per day.

Hope this helps!

 

[Melvin Purvis]

My spreadsheet is obviously much larger than my example, closing out about 15
megabytes in size.

I took your example and applied it to my real world data, and it worked.

So, for that I thank you very, very much!

Next, I was able to take sumproduct, using your example, and I was able to
apply it to a couple of other columns that I have. I got it to work there also.

One problem is, even though I can manipulate it, I still don't really
understand how or why sumproduct works. When I try reading that page that you
included, it just doesn't help me. To paraphrase Gary Larson, perhaps it's time
I face reality, I'm not really a rocket scientist...

Anyway, I have another little problem that has cropped up. It applies to
something completely different in my spreadsheet, but we can apply the same
example. If the column "Ticket #" happens to contain data that is not numeric,
how would I go about a mutliple lookup? Will sumproduct work with non-numeric
entries? By none numeric, let's say the ticket numbers looked like 4567K,
6787R, and 5678W.

Again, thanks for any assistance!