Originally Posted by

Good Morning,

I thought about it some more, and I think you could do it just as easily with formulas. First, add a formula to any available column on the SS sheet (I used column S in the example below, but you may use a different one). In row 2 of that column enter the formula and fill down:

=C2&H2&COUNTIFS($C$2:C2, C2, $H$2:H2, H2)

The result will be a string with the model number, Yes/No, and a running count. For example, "7945Yes1" or "7945Yes23".

Now, go to the tabs with your data and enter an INDEX formula to MATCH the items you want and pull in the correct column. In this example, I placed the formula in Cell B2 and referenced the SS tab range A:S, where column S contains the formula mentioned earlier in this post. The formula to enter is:

=IF(ISNA(MATCH("7945Yes" & ROW(A1), Sheet1!S:S,0)), "", INDEX(SS!A:S, MATCH("7945Yes" & ROW(A1), SS!S:S,0), 4))

In this formula, any "#N/A" values return a null string. Since the formula starts in row 2, I use a relative reference to the row of cell A1 to increment the counter (the formula ROW(A1) returns "1", so as you copy it down, the number increases by one each row).

If there is a match for the model number + "Yes" + the counter, then the formula will index against columns A:S on the SS tab and use the MATCH function to pull in the relevant row. Finally, the "4" at the end is telling the Index function what column to pull in. In this case, I wanted column D, which is the 4th column in the range A:S.

Take care,
Ben