2 stage vlookup or option

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have a vlookup question or suggestions to an alternative
are welcome.

Column 1 column 2 column 3 column 4 column 5

sku offer description price offer amt
8805 1 roses red $19.95 1 plant
8806 1 painted daises $20.95 1 pant
8806 2 painted daises $19.95 2 plants
8806 3 pianted daises $10.95 3 plants
8807 1 lilies $ 7.95 1 plants

Abocve is some sample dat ato try and clarify my question

The question is can I do a look that will treat the tree
8806 sku's as 3 different items do to the offer column
having a different number for each of the 3 duplicate sku's

Th issue is that I have th eabove work sheet with mnay
sku's that are duplicated 12 to 3 times but with different
info for each line - I need to then get the 3 8806 lines
of data over to another sheet where there is only a single
occurance of the 8806.

If it is not worht automatin gtell me and I will sart
copying manually but anything to improve this siuation
will help.

Thank you (e-mail address removed)
 
What do you mean you need to get them over to another sheet where you have only
one occurrence. What is the main aim of what you are trying to achieve?? Data
/ Filter / Autofilter might help here, whereas a Pivot Table may also help -
Depends on what you are really trying to accomplish.
 
It sounds like what you really want is pivot tables. You can create one
pivot table to show you data based on a selection in the sku column and
sorted by the offer column.

As an answer to your actual question it is possible to do a 2 stage vlookup.
In this case it would be difficult because you must be sorted on the column
you are using for a lookup. You also have to define ranges for sets of data
which in your case do not appear like they are going to be static.

bob
 
thanks for looking at it
what I need is to move the sku 's and there data over to
another sheet where the sku only occurs once but if there
is three 8806's I need them to appear on three rows

So to try and simplify
sheet 1 has one occurance of 8806 , I need to do a vlookup
to sheet 2 where there could be 1 to 3 occurances of 8806

I need to pull all occurances into sheet 1

sku offer
8806 1
8806 2
8806 3

This is beginning to sound very complex - I am thinking I
may only be able to cut and paste

My company uses duplicated part numbers in the sku field
which is killing me.

Thanks for anything you can add
 
You could apply a subtotal function to the first list
(Data->subtotals) then use the subtotals for the look up.
you would have to alter your total sheet a bit to
incorporate the "8806 Total" which is the result for the
subtotal function.
 
thanks

so there is no such thing as a vlook up that uses 2
columns - in this case the sku column which has duplicates
and the 2nd column that has a differenciation code 1, 2,
3, etc

thanks
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, with 8806
in Cell A20 on Sheet5 and your data in A1:B6 on Sheet6:

=VLookups(A20,Sheet6!A2:B6,{1,2}) array entered into a range on Sheet5
large enough to accommodate the output.

Alan Beban
 
thanks for the time

I can not do subtotals - need to pull 1 to 3 lines of data
each with same sku to a sheet where the skiu is only there
one time.

If it is there once then needs to appear 2 more times

I think this is to nasty to automate.

T
 
I agree you are absolutely correct

I will have to look at pivots after the first pass do to
deadlines

fear not I will investigate thoroughly.

thanks again
 
Back
Top