Extract sorted list without duplicates from unsorted list with duplicates

Globe Trotter Medic

New Member
Joined
Sep 25, 2018
Messages
2
I am new to the forum and new to Excel.

Work book contains 2 sheets,
Sheet 1
Column C, beginning at C2 contains names, that may or may not duplicate,
Column D, beginning at D2 contains Company names, that may or may not duplicate,
Jumping over to Column J, J2 contains a numerical value.

Sheet 2,
I want to have Column B, starting at B2, list the names from Sheet 1 without duplicates, Alphabetical Order, and auto populate as Sheet 1 is manually populated
Column C, starting at C2 list the company name from Sheet 1 Column D that corresponds to the individuals name,
Column D, starting at D2, have the sum of numerical values that correspond to the individual.

Currently on Sheet 2, B2, I have

=IFERROR(LOOKUP(2,1/(COUNTIF($B$1:B1,RECORDS!$C$2:$C$1999)=0),RECORDS!$C$2:$C$1999),"")

I now have "0" in B2 and the Individual names (NOT alphabetized) in subsequent cells of Column B.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hello,

As an example ... with your data in cells A2:A8

you would use the following Array Formula

Code:
IFERROR(INDEX(A2:A8,MATCH(0,COUNTIF(A2:A8,"<"&A2:A8)-SUM(COUNTIF(A2:A8,B$1:B1)),0)),"")

Hope this will help
 
Upvote 0
James006 - PERFECT! This worked perfectly for the Names.

Thank you!

Hello,

As an example ... with your data in cells A2:A8

you would use the following Array Formula

Code:
IFERROR(INDEX(A2:A8,MATCH(0,COUNTIF(A2:A8,"<"&A2:A8)-SUM(COUNTIF(A2:A8,B$1:B1)),0)),"")

Hope this will help
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

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