Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with multiple same name entries
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with multiple same name entries
Melvin Purvis wrote:
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with multiple same name entries
smartin wrote:
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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with multiple same name entries
Melvin Purvis wrote:
smartin wrote: 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... 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with multiple same name entries
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! smartin wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple entries / multiple criteria | Excel Discussion (Misc queries) | |||
Combine Multiple Entries with differing amounts of entries | Excel Worksheet Functions | |||
how to do linear interpolation between entries in lookup table | Excel Discussion (Misc queries) | |||
Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
How to lookup when range contains multiple identical entries? | Excel Discussion (Misc queries) |