VLookup or Index Match or ???

[Guest]

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.

 

[Max]

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