Index Small If Row Horizontally with no Duplicates

[lbrockmeier]

Hi there,

Would really appreciate any help with this!

I have 2 sheets: Combined Data and VGBB Summary. The snapshot below is VGBB summary. What I need to do is bring over all of the prices associated with each category (e.g. Accessories) to the right of the category. My issue is that there are multiple duplicates of prices for each category. I would like for prices to populate to the right of the category with no duplicates!

Category
Accessories	7.99	7.99	7.99	7.99
Pants	19.99
Shirts	14.99

<tbody>
</tbody>

Here is the formula I am currently using:

=IFERROR(INDEX('Combined Data'!$A$6:$Q$11959,SMALL(IF('Combined Data'!$Q$7:$Q$11959='VGBB Summary (2)'!$A2,ROW('Combined Data'!$Q$7:$Q$11959)),ROW(1:1))-1,13),"")

Any help is greatly appreciated

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Category
2	Accessories	7.99	8.00
3	Pants	25.00	26.00
4	Shirts	15.50
Sheet1

Excel Workbook
	A	B
1	Category	Price
2	Accessories	7.99
3	Pants	25.00
4	Shirts	15.50
5	Accessories	7.99
6	Pants	26.00
7	Shirts	15.50
8	Accessories	8.00
9	Pants	26.00
10	Shirts	15.50
Sheet2

 

[lbrockmeier]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Sheet1

	A	B	C	D
1	Category
2	Accessories	7.99	8.00
3	Pants	25.00	26.00
4	Shirts	15.50

<colgroup><col style="width:30px; "><col style="width:92px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 {=IFERROR(INDEX(Sheet2!$B$2:$B$10,SMALL(IF(FREQUENCY(IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10),Sheet2!$B$2:$B$10),ROW(Sheet2!$B$2:$B$10)-ROW(Sheet2!$B$2)+1),COLUMNS($B$2:B2))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Sheet2

	A	B
1	Category	Price
2	Accessories	7.99
3	Pants	25.00
4	Shirts	15.50
5	Accessories	7.99
6	Pants	26.00
7	Shirts	15.50
8	Accessories	8.00
9	Pants	26.00
10	Shirts	15.50

<colgroup><col style="width:30px; "><col style="width:109px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you so much for this! It worked perfectly. However, is there a way to update the formula so that prices increase from left to right in ascending order?

Right now, this is what I am seeing:

	A	B	C	D
1	Category	Price
2	Accessories	24.99	16.99	12.99
3	Pants	24.99	16.99	19.99
4	Shorts

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">AB1CategoryPrice2Accessories7.993Pants25.004Shirts15.505Accessories7.996Pants26.007Shirts15.508Accessories8.009Pants26.0010Shirts15.50</body>

 

[AhoyNC]

Sorry I don't know away to sort in the order you want. Other than to sort your data sheet first.
Someone else on the forum may know a formula way to do this.

 

[Aladin Akyurek]

In B2 of Sheet1 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($B2:B2)>SUM(IF(FREQUENCY(IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10),IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10)),1)),"",MIN(IF(ISNA(MATCH(IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10),$A2:A2,0)),IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10))))

 

[lbrockmeier]

In B2 of Sheet1 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($B2:B2)>SUM(IF(FREQUENCY(IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10),IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10)),1)),"",MIN(IF(ISNA(MATCH(IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10),$A2:A2,0)),IF(Sheet2!$A$2:$A$10=$A2,Sheet2!$B$2:$B$10))))

Thank you! Works perfectly!

 

[Aladin Akyurek]

Thank you! Works perfectly!

You are very welcome. ​