Lookup Formula Question

[JWNJ]

I have a file with 10,000 plus rows of date that I need to prepare a running
summary.

Column A = site location
Column B = shelf name
Column C = slot number
Column D = status of slot ~ spare or used

Every shelf has 40 slots so column A & B repeat themselves 40 consecutive
times to show a complete inventory for the shelf.

In a separate tab I need to summarize the spare/used slots by site by shelf
name. Ideally I would like to show slots 1 - 40 in row 1 and site/shelf name
in column A. So if location USA , shelf 1 slot 1 was used cell B2 would
output "used".

Any formula suggestions would be much appreciated.

Thanks.

 

[Max]

Think an array-entered, multi-criteria index/match would do it

Example, assuming source data as posted is in sheet: x,
data from row 2 to 10000

Then in another sheet, assuming
Site Location is in B1, eg: USA
ShelfName in B2, eg: xxx
SlotNums are listed in A3 down, ie: 1,2,3,...
(B1 & B2 could house data validation lists)

Put this in B3, array-enter by pressingCTRL+SHIFT+ENTER instead of just
pressing ENTER
=INDEX(x!D$2$10000,MATCH(1,(x!A$2:A$10000=$B$1)*(x!B$2:B$10000=$B$2)*(x!C$2:C$10000=A3),0))
Copy B3 down. Adapt the ranges to suit your actual data extent expected.

For performance reasons, as it's quite calc-intensive, you might want to
switch the book's calc mode to Manual. Then press F9 only whenever required.

And if you need an error trap to return neat looking blanks: "" instead of
ugly #N/As, you could use this kind of construct:
=IF(ISNA(MATCH),"",INDEX/MATCH)

viz, for the example above, it'll be this in B3, array-entered
=IF(ISNA(MATCH(1,(x!A$2:A$10000=$B$1)*(x!B$2:B$10000=$B$2)*(x!C$2:C$10000=A3),0)),"",INDEX(x!D$2$10000,MATCH(1,(x!A$2:A$10000=$B$1)*(x!B$2:B$10000=$B$2)*(x!C$2:C$10000=A3),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,300 Files:356 Subscribers:53
xdemechanik