Finding the three lowest scores

[spydey]

So I have, what I think, is a somewhat complicated scenario that I need to solve. I have bits and pieces but can't seem to get it to all come together in to the end result I am seeking.

I have a single workbook.
Two worksheets.

Sheet 1:
ItemNames in column A and Locations in column B.
There are 80+ unique ItemNames, and 53 unique Locations.
The ItemNames starts in A3 and the Locations start in B3
Row 2 contains the column headers.

Sheet 2:
ItemNames in column A and Locations in column B.
There are 80+ unique ItemNames, and 53 unique Locations.
They are not all in the same order as Sheet 1.
The ItemNames starts in A3 and the Locations start in B3
Row 2 contains the column headers.
Columns I through Q contains numerical data for each combination of ItemName and Location.

I hope that helps to some degree.

I need to do the following:

Give me the headers of the columns that contain the 3 lowest values for each ItemName & Location combination.

Example: In sheet 1, cell D3:F3, I need the names of the columns from Sheet 2 that contain the lowest values for the same ItemName & Location combo as found in Sheet 1 Cells A3 & B3.

I have used the following, in sheet 1 cell D3, before to get the headers of the columns that contain the lowest value:

=INDEX(Sheet2!$I$2:$Q$2,0,MATCH(SMALL(Sheet2!$I3:$Q3,COLUMNS($D$3:D3)),Sheet2!$I3:$Q3,0))

I then fill this over through F3.

But I have two issues with using this:

1. It doesn't allow me to get the data for the same ItemNames & Location combo as in Sheet1. The ItemNames & Locations from sheet1 may not be in the same order as sheet2. I was thinking of using an Index(Match, as I have for other things, but am not sure how to incorporate it to use the above formula.

2. If any of the 3 lowest values are the same, then I get the same header, the first one in the series, for any of the same values, even though the headers are all different. Example: values of 0, 2, 0. My headers would return as Header1, Header2, & Header1; when it should be Header1, Header2, Header3.

Hopefully that all makes sense!! I am a little confused myself, so I hope I didn't pass that on in my writeup! hahahaha

Thanks as always.

-Spydey

So in essence I guess it would be a formula whose output would be this:

Give me the unique column headers of the 3 lowest values from Sheet2 where ItemName & Location match the ItemName & Location from Sheet 1. The results would be placed in Sheet 1.

 

[Eric W]

I'm still pondering how to do this, but a few questions: what are the numbers like in I:Q on sheet2? Integers, decimals? If decimal, is there a fixed number of digits? And of course listing the headers from least to 3rd least would be best, but would having the 3 least headers in some order work? And do you require a formula, or would a UDF (user-defined function in VBA) work for you?

 

[spydey]

Eric W, thanks for taking the time to look at this with me! :D

The values in I:Q are displayed as percentages, so in reality, they are decimals derived from a ratio formed by a formula. It goes up to two decimal places for the percentages, e.g. 83.27%. The issue is that some of the values are 0%, which is ok, but when there are multiple 0%, then finding the lowest three values and returning the associated column headers, doesn't pan out the way it should. I get the header of the first 0% for any and all 0% columns. I imagine that the same would happen for any column where the values are exactly the same.

Regarding the order of least to third least, it doesn't really matter to me, as they could be in any order, as long as I get the headers of the 3 lowest values. Formula would probably be better, but I can work with a UDF in VBA, no worries.

Again, thanks for your time and willingness to assist me with this. It is like I have two parts of a puzzle but I can't get them to fit. I imagine there are 3 other parts that I need first! hahahaha

Take care.

-Spydey

 

[Marcelo Branco]

Maybe...(assumes unique ItemNames,not duplicates)

Array formula in D3 of Sheet1 copied across and down
=INDEX(Sheet2!$I$2:$Q$2,SMALL(IF(ISNA(MATCH(Sheet2!$I$2:$Q$2,$C3:C3,0)),IF(INDEX(Sheet2!$I$3:$Q$100,MATCH($A3,Sheet2!$A$3:$A$100,0),0)=SMALL(INDEX(Sheet2!$I$3:$Q$100,MATCH($A3,Sheet2!$A$3:$A$100,0),0),COLUMNS($D3:D3)),COLUMN(Sheet2!$I$2:$Q$2)-COLUMN(Sheet2!$I$2)+1)),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Eric W]

This is what I came up with:

Put this in D3 of sheet1, copied across and down:

=INDEX(Sheet2!$I$2:$Q$2,MIN(IF(INDEX(Sheet2!$I$3:$Q$18,MATCH($A3&"|"&$B3,Sheet2!$A$3:$A$18&"|"&Sheet2!$B$3:$B$18,0),0)=SMALL(INDEX(Sheet2!$I$3:$Q$18,MATCH($A3&"|"&$B3,Sheet2!$A$3:$A$18&"|"&Sheet2!$B$3:$B$18,0),0),COLUMNS($D3:D3)),IF(COUNTIF($C3:C3,Sheet2!$I$2:$Q$2)=0,COLUMN(Sheet2!$I$2:$Q$2)-COLUMN(Sheet2!$I$2)+1))))

Confirm with Control+Shift+Enter.

The headings should be returned in order, low to high, and allows for ties. Change the ranges, especially the last row of your table, to match your sheet.

 

[spydey]

Maybe...(assumes unique ItemNames,not duplicates)

Array formula in D3 of Sheet1 copied across and down
=INDEX(Sheet2!$I$2:$Q$2,SMALL(IF(ISNA(MATCH(Sheet2!$I$2:$Q$2,$C3:C3,0)),IF(INDEX(Sheet2!$I$3:$Q$100,MATCH($A3,Sheet2!$A$3:$A$100,0),0)=SMALL(INDEX(Sheet2!$I$3:$Q$100,MATCH($A3,Sheet2!$A$3:$A$100,0),0),COLUMNS($D3:D3)),COLUMN(Sheet2!$I$2:$Q$2)-COLUMN(Sheet2!$I$2)+1)),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Thanks Marcelo. Question for you: why are we referencing cells in column C? See the bolded part in your quote above.

 

[spydey]

Eric, thanks for the input!

Question for you: Why are we referencing column C? e.g ......If(COUNTIF($C3:C3, .........

-Spydey

 

[Eric W]

Eric, thanks for the input!

Question for you: Why are we referencing column C? e.g ......If(COUNTIF($C3:C3, .........

-Spydey

In both of our formulas, the way we made sure that we don't duplicate entries is by checking to see if we have already placed the heading in the previous selections. So the formula in F3 needs to check D3 and E3, the formula in E3 needs to check D3. Technically speaking, the formula in D3 doesn't need to check anything. So we could write one formula in D3, and a different formula for E3:F3. But in the interest of fewer formulas, we wrote a formula where all the cells check the C column too. So the D3 formula checks the C3 cell, and as you drag the formula to the right, each succeeding formula checks one additional cell. It doesn't matter what's in the C column, as long as it's not one of the headers from sheet2. Marcelo used a MATCH, and I used COUNTIF to check the previous cells.

 

[spydey]

Thanks for the response Eric. Makes total sense. I figured that is what both of you were doing but I wasn't really too sure.

I am going to play around with both of the formulas and report back.

Thanks again to both you and Marcelo for your time and assistance.

-Spydey

 

[spydey]

This is what I came up with:

Put this in D3 of sheet1, copied across and down:

=INDEX(Sheet2!$I$2:$Q$2,MIN(IF(INDEX(Sheet2!$I$3:$Q$18,MATCH($A3&"|"&$B3,Sheet2!$A$3:$A$18&"|"&Sheet2!$B$3:$B$18,0),0)=SMALL(INDEX(Sheet2!$I$3:$Q$18,MATCH($A3&"|"&$B3,Sheet2!$A$3:$A$18&"|"&Sheet2!$B$3:$B$18,0),0),COLUMNS($D3:D3)),IF(COUNTIF($C3:C3,Sheet2!$I$2:$Q$2)=0,COLUMN(Sheet2!$I$2:$Q$2)-COLUMN(Sheet2!$I$2)+1))))

Confirm with Control+Shift+Enter.

The headings should be returned in order, low to high, and allows for ties. Change the ranges, especially the last row of your table, to match your sheet.

Eric,

Question for you.

In the MATCH( function, why are we using a pipe, "|", to combine the search of the Item & Location (column A & column B)? Just curious ... I am trying to decipher the formula you came up with and understand it better.

Thanks!

-Spydey

P.S. Do you think that this:

.....MATCH(1,(Sheet2!$A$3:$A$18=$A3)*(Sheet2!$B$3:$B$18=$B3),0)........

would give me the same input/output/result as your section of the equation:

..... MATCH($A3&"|"&$B3,Sheet2!$A$3:$A$18&"|"&Sheet2!$B$3:$B$18,0) .....

Thoughts?