Index Small If Row Horizontally with no Duplicates

L

lbrockmeier

New Member
Joined
Dec 18, 2017
Messages
7
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!
[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]7.99[/TD]
[TD]7.99[/TD]
[TD]7.99[/TD]
[TD]7.99[/TD]
[/TR]
[TR]
[TD]Pants[/TD]
[TD]19.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shirts[/TD]
[TD]14.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
ABCD
1Category
2Accessories7.998.00
3Pants25.0026.00
4Shirts15.50
Sheet1
Excel Workbook
AB
1CategoryPrice
2Accessories7.99
3Pants25.00
4Shirts15.50
5Accessories7.99
6Pants26.00
7Shirts15.50
8Accessories8.00
9Pants26.00
10Shirts15.50
Sheet2
 
Upvote 0
AhoyNC said:
Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Sheet1

ABCD
Accessories
Pants
Shirts

<colgroup><col style="width:30px; "><col style="width:92px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Category[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]7.99[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]8.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]25.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]26.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]15.50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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

AB
Accessories
Pants
Shirts
Accessories
Pants
Shirts
Accessories
Pants
Shirts

<colgroup><col style="width:30px; "><col style="width:109px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Category[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Price[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]7.99[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]25.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]15.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]7.99[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]26.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]15.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]8.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]26.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]15.50[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


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:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Accessories[/TD]
[TD]24.99[/TD]
[TD]16.99[/TD]
[TD]12.99[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pants[/TD]
[TD]24.99[/TD]
[TD]16.99[/TD]
[TD]19.99[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Shorts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





****** 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>
 
Upvote 0
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.
 
Upvote 0
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))))
 
Upvote 0
Aladin Akyurek said:
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))))
Click to expand...


Thank you! Works perfectly!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Combining Index, small, if, row
Replies
4
Views
657
Translationguy
T
brockk
  • Solved
Removing blank rows including those with "FALSE" data
Replies
7
Views
321
Domenic
D
P
  • Question Question
VBA with removing duplicate rows, with the addition of sum and concat
Replies
2
Views
689
sijpie
sijpie
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
332
felixstraube
F
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
574
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top