kmc said:
this worked great. Can you explain in simple English what it's doing?
"Simple" English? Maybe...
Assume you have a list of items (named "Item_List") and codes (named
"Item_Codes"). The combination of item and code is unique, however the items
could be repeated multiple times in the list.
You would like a new list of each unique item in a column, with the associated
codes to the right in the same row.
The list of unique items can be obtained with the following array formula
(commit with CTRL+SHIFT+ENTER) in A2 and copied down until blanks are returned:
=IF(ROWS($1:1)>SUM(1/COUNTIF(Item_List,Item_List)),"",
INDEX(Item_List,MATCH(0,COUNTIF($A$1:A1,Item_List),0)))
ROWS($1:1) will return the row number of your new unique item list as it is
copied down the column.
COUNTIF(Item_List,Item_List) returns an array of the number of times each entry
is found in Item_List. If Item_List was
{A, B, C, C, B, B, C, A, B}
then the COUNTIF() would return
{2, 4, 3, 3, 4, 4, 3, 2, 4}
because A was found twice, B four times and C three times. The "1/" turns that into
{1/2, 1/4, 1/3, 1/3, 1/4, 1/4, 1/3, 1/2, 1/4}
and the SUM() adds them all up to get the total number of unique items in the
list, which in this case is 3. If that is greater than the current row number
of your unique list, there should be an item returned in this cell. Otherwise,
it is left blank ("").
COUNTIF($A1:A1,Item_List) returns a series of 1's and 0's corresponding to
whether the items in Item_List were listed previously in your unique list. In
the previous example, on the *second* row of the unique list it would return
{1, 0, 0, 0, 0, 0, 0, 1, 0}
MATCH(0,) then returns the position within Item_List of the first item that was
not already listed (2), and INDEX(Item_List) returns that specific item (B).
The first associated code for each item is returned be entering the following in
B2 and copy down until blanks are returned:
=IF(A2="","",INDEX(Item_Codes,MATCH($A2,Item_List,0)))
MATCH() returns the row number of the first occurrence within Item_List of the
unique item found on that row in column A. INDEX() uses that row number to find
the corresponding code in Item_Codes.
The rest of the associated codes are obtained by entering this array formula
(commit with CTRL+SHIFT+ENTER) in C2 and copying down and across as needed:
=IF(COLUMN(B$1)>COUNTIF(Item_List,$A2),"",
INDEX(
INDIRECT("'"&CELL("filename",Item_Codes)&
"'!R"&MATCH($A2&B2,Item_List&Item_Codes,0)+ROW(Item_Codes)&
"C"&COLUMN(Item_Codes)&
":R"&ROWS(Item_Codes)+ROW(Item_Codes)-1&
"C"&COLUMN(Item_Codes),FALSE),
MATCH($A2,
INDIRECT("'"&CELL("filename",Item_List)&
"'!R"&MATCH($A2&B2,Item_List&Item_Codes,0)+ROW(Item_List)&
"C"&COLUMN(Item_List)&
":R"&ROWS(Item_List)+ROW(Item_List)-1&
"C"&COLUMN(Item_List),FALSE),
0)))
(This formula has been modified to eliminate hard-coded references to the
location of the source lists.)
The first portion of the formula determines if the current column number within
your new table is greater than the number of times the item in that row was
found in Item_List. If not, there should be a code listed in the cell.
Otherwise it is left blank ("").
The "outer" MATCH() and INDEX() work the same as in the previous formula.
The INDIRECT() formulas are used to redefine Item_List and Item_Codes to start
below the values already listed. The "inner" MATCH() is searching the
combination of Item_List & Item_Codes and locating the combination of item &
code from this row (found in the cells to the left) to determine that new
starting point. CELL() tells Excel what worksheet contains Item_List and
Item_Codes. The ROW(), ROWS() and COLUMN() functions convert the positions of
Item_List and Item_Codes to numbers that INDIRECT() can evaluate. The FALSE at
the end of the INDIRECT() formulas tells Excel that the reference being built is
in R1C1 format.
