Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup or Index Match or ???
I have a list of approx 500 publications. Each of these publications has a
unique number (ISBN). These publications are all sold individually to numerous suppliers. In addition, each of the publications may be a part of a pack which is made up of perhaps 1, 2 or 3 other publications. This pack is given its own ISBN. EG ISBN Portfolio/Product Author 0455221669 Annotated Trade Practices Act Miller 0455223157 Annotated Trade Practices Act Miller 0455223319 Annotated Trade Practices Act Miller In this example the first ISBN is the individual and the next two are pack ISBN's. The sales data is held in separate sheets. This data is treated differently for Pack sales but needs to be consolidated for each month by the individual ISBN number. I need to find a way to consolidated the sales data by referencing only the individual product ISBN. If this is unclear please let me know or happy to email an example of the data. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup or Index Match or ???
One play ..
Assuming the other ISBNs pegged to the "individual" ISBN for any publication can be defined (i.e. identified) by the first 6 digits of the ISBN (example the string: "045522" in your sample data) In sheet: X, in cols A to D, data from row2 down, we have (Sales quantity in col D) ISBN Portfolio/Product Author SaleQty 0455221669 Annotated Trade Miller 2 0455223157 Annotated Trade Miller 1 0455223319 Annotated Trade Miller 1 and in Sheet: Y, we have the list of *only* the "individual" ISBNs in cols A to C, data from row2 down ISBN Portfolio/Product Author 0455221669 Annotated Trade Miller we could put in D2: =SUMPRODUCT(--(LEFT(A2,6)=LEFT(X!$A$2:$A$500,6)),X!$D$2:$D$500) and copy D2 down Col D will return the desired totals from X For the sample data above, we'd get in D2: 4 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "KopRed" wrote in message ... I have a list of approx 500 publications. Each of these publications has a unique number (ISBN). These publications are all sold individually to numerous suppliers. In addition, each of the publications may be a part of a pack which is made up of perhaps 1, 2 or 3 other publications. This pack is given its own ISBN. EG ISBN Portfolio/Product Author 0455221669 Annotated Trade Practices Act Miller 0455223157 Annotated Trade Practices Act Miller 0455223319 Annotated Trade Practices Act Miller In this example the first ISBN is the individual and the next two are pack ISBN's. The sales data is held in separate sheets. This data is treated differently for Pack sales but needs to be consolidated for each month by the individual ISBN number. I need to find a way to consolidated the sales data by referencing only the individual product ISBN. If this is unclear please let me know or happy to email an example of the data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Vlookup, Match or Index? | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Vlookup, index, match? | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |