Return each unique value based on other column criteria

C

cra5hdown

New Member
Joined
Oct 4, 2016
Messages
19
So the table i have looks a little like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Rich[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Rich[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Rich[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
table is actually much more extensive.

what im looking for, is to setup another table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rate 1[/TD]
[TD]Rate 2[/TD]
[TD]Rate 3[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]=[/TD]
[TD]=[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Rich[/TD]
[TD]=[/TD]
[TD]=[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]=[/TD]
[TD]=[/TD]
[TD]=[/TD]
[/TR]
</tbody>[/TABLE]
so i need a formula that looks in table1 and returns each unique rate for each person in the table2 arrangement
  • there may be upto 10 unique rates per person
  • there may be hundreds of rows with the same rate
  • i know about the function to remove duplicates, but its not really feasible given the number of people and having to do it each month and constant changes in rates
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
One possibility:

ABCDEFGHI
NameRates
Harry
Rich
Tom

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Rate[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]

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

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

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

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

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

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

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

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

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($A$2:$A$20,"<"&$A$2:$A$20)-SUM(COUNTIF($E$1:$E1,$A$2:$A$20)),0)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$2:$A$20=$E2,IF(COUNTIF($E2:E2,$B$2:$B$20)=0,$B$2:$B$20)),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in E2, confirm with Control+Shift+Enter. Drag down as needed. This will create a unique sorted list. Then put in the F2 formula, confirm with Control+Shift+Enter. Drag down and to the right as needed. The will show the rates in ascending order.

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
495
Anthony47
A
willow1985
  • Question Question
Assistance with finding top 2 repeated values based on 2 criteria (LET,UNIQUE,FILTER,TAKE,SORT,HSTACK,COUNTIF formula)
Replies
3
Views
194
Cubist
Cubist
megera716
  • Solved
Return table column value for all rows with one lookup value
Replies
3
Views
449
Fluff
Fluff
2
  • Question Question
Filter or Index based on cell value and column header
Replies
3
Views
253
Peter_SSs
Peter_SSs
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
405
zero269
Z

Forum statistics

Threads
1,224,918
Messages
6,181,743
Members
453,064
Latest member
robatthe2A

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