Simplifying a list of duplicate cells on another sheet

[wesleyterrill]

Hey guys! So I have a large set of data that I've imported into Excel. It is a list of thousands of individuals, the companies they work for, and other information. What I'd like to do is create a list of each individual company and their respective info. But for example, at ABC Co. there are multiple individuals that work for them. Every formula I've tried lists each company multiple times, instead of just once. I've simplified the data that I'm working with in the table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Company[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]Personal #[/TD]
[TD="align: center"]Company #[/TD]
[TD="align: center"]Personal Address[/TD]
[TD="align: center"]Company Address[/TD]
[/TR]
[TR]
[TD="align: center"]ABC Co.[/TD]
[TD="align: center"]Sheryl Fahn[/TD]
[TD="align: center"]VP of Sales[/TD]
[TD="align: center"]555-1985[/TD]
[TD="align: center"]555-0000[/TD]
[TD="align: center"]2185 Touring Ct.[/TD]
[TD="align: center"]123 Main St.[/TD]
[/TR]
[TR]
[TD="align: center"]ABC Co.[/TD]
[TD="align: center"]Jim Baker[/TD]
[TD="align: center"]Director of Sales[/TD]
[TD="align: center"]555-2669[/TD]
[TD="align: center"]555-0000[/TD]
[TD="align: center"]9641 Runway Ln.[/TD]
[TD="align: center"]123 Main St.[/TD]
[/TR]
[TR]
[TD="align: center"]Delta[/TD]
[TD="align: center"]Brian Totilo[/TD]
[TD="align: center"]Sales Manager[/TD]
[TD="align: center"]555-7894[/TD]
[TD="align: center"]555-2222[/TD]
[TD="align: center"]5840 Regal Rd.[/TD]
[TD="align: center"]100 McKinney Ln.[/TD]
[/TR]
[TR]
[TD="align: center"]ABC Co.[/TD]
[TD="align: center"]Keith English[/TD]
[TD="align: center"]COO[/TD]
[TD="align: center"]555-4234[/TD]
[TD="align: center"]555-0000[/TD]
[TD="align: center"]2120 South Main St.[/TD]
[TD="align: center"]123 Main St.[/TD]
[/TR]
[TR]
[TD="align: center"]Capstone[/TD]
[TD="align: center"]Barry Martin[/TD]
[TD="align: center"]Territory Manager[/TD]
[TD="align: center"]555-0076[/TD]
[TD="align: center"]555-6666[/TD]
[TD="align: center"]5135 Congress Ave.[/TD]
[TD="align: center"]650 Kings Ct.[/TD]
[/TR]
[TR]
[TD="align: center"]Delta[/TD]
[TD="align: center"]Kim Ducanes[/TD]
[TD="align: center"]VP of Operations[/TD]
[TD="align: center"]555-7246[/TD]
[TD="align: center"]555-2222[/TD]
[TD="align: center"]8120 Vista Ridge Dr.[/TD]
[TD="align: center"]100 McKinney Ln.[/TD]
[/TR]
[TR]
[TD="align: center"]Delta[/TD]
[TD="align: center"]Chris Barnhill[/TD]
[TD="align: center"]Director of Operations[/TD]
[TD="align: center"]555-1515[/TD]
[TD="align: center"]555-2222[/TD]
[TD="align: center"]1167 Rockport Dr.[/TD]
[TD="align: center"]100 McKinney Ln.[/TD]
[/TR]
[TR]
[TD="align: center"]Capstone[/TD]
[TD="align: center"]Phil Caporal[/TD]
[TD="align: center"]CEO[/TD]
[TD="align: center"]555-6881[/TD]
[TD="align: center"]555-6666[/TD]
[TD="align: center"]3455 Delgado Dr.[/TD]
[TD="align: center"]650 Kings Ct.[/TD]
[/TR]
[TR]
[TD="align: center"]ABC Co.[/TD]
[TD="align: center"]David Loy[/TD]
[TD="align: center"]Sales Manager[/TD]
[TD="align: center"]555-5058[/TD]
[TD="align: center"]555-0000[/TD]
[TD="align: center"]8010 Winchester Ln.[/TD]
[TD="align: center"]123 Main St.[/TD]
[/TR]
[TR]
[TD="align: center"]Delta[/TD]
[TD="align: center"]Patricia White[/TD]
[TD="align: center"]VP of Sales[/TD]
[TD="align: center"]555-5077[/TD]
[TD="align: center"]555-2222[/TD]
[TD="align: center"]9631 Express Ct.[/TD]
[TD="align: center"]100 McKinney Ln.[/TD]
[/TR]
</tbody>[/TABLE]

There are 3 individual companies listed above, with several employees at each company. While avoiding duplications, I'd like to create a formula on the next sheet that would yield a table such as the one below:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Account (Company)[/TD]
[TD="align: center"]Company Phone #[/TD]
[TD="align: center"]Company Address[/TD]
[/TR]
[TR]
[TD="align: center"]ABC Co.[/TD]
[TD="align: center"]555-0000[/TD]
[TD="align: center"]123 Main St.[/TD]
[/TR]
[TR]
[TD="align: center"]Delta[/TD]
[TD="align: center"]555-2222[/TD]
[TD="align: center"]100 McKinney Ln.[/TD]
[/TR]
[TR]
[TD="align: center"]Capstone[/TD]
[TD="align: center"]555-6666[/TD]
[TD="align: center"]650 Kings Ct.[/TD]
[/TR]
</tbody>[/TABLE]

Let me know if you have any ideas, thanks in advance for the help!

 

[MarcelBeug]

Tab DATA - Remove duplicates - uncheck all columns but the 3 company fields, press OK and remove the other columns.
You may want to try in a copy of your workbook / worksheet first.

 

[FDibbins]

If the "remove dups" is not an option, try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Company​

[/td][td]

Name​

[/td][td]

Title​

[/td][td]

Personal #​

[/td][td]

Company #​

[/td][td]

Personal Address​

[/td][/tr]

[tr][td]

2​

[/td][td]

ABC Co.​

[/td][td]

Sheryl Fahn​

[/td][td]

VP of Sales​

[/td][td]

555-1985​

[/td][td]

555-0000​

[/td][td]

2185 Touring Ct.​

[/td][/tr]

[tr][td]

3​

[/td][td]

ABC Co.​

[/td][td]

Jim Baker​

[/td][td]

Director of Sales​

[/td][td]

555-2669​

[/td][td]

555-0000​

[/td][td]

9641 Runway Ln.​

[/td][/tr]

[tr][td]

4​

[/td][td]

Delta​

[/td][td]

Brian Totilo​

[/td][td]

Sales Manager​

[/td][td]

555-7894​

[/td][td]

555-2222​

[/td][td]

5840 Regal Rd.​

[/td][/tr]

[tr][td]

5​

[/td][td]

ABC Co.​

[/td][td]

Keith English​

[/td][td]

COO​

[/td][td]

555-4234​

[/td][td]

555-0000​

[/td][td]

2120 South Main St.​

[/td][/tr]

[tr][td]

6​

[/td][td]

Capstone​

[/td][td]

Barry Martin​

[/td][td]

Territory Manager​

[/td][td]

555-0076​

[/td][td]

555-6666​

[/td][td]

5135 Congress Ave.​

[/td][/tr]

[tr][td]

7​

[/td][td]

Delta​

[/td][td]

Kim Ducanes​

[/td][td]

VP of Operations​

[/td][td]

555-7246​

[/td][td]

555-2222​

[/td][td]

8120 Vista Ridge Dr.​

[/td][/tr]

[tr][td]

8​

[/td][td]

Delta​

[/td][td]

Chris Barnhill​

[/td][td]

Director of Operations​

[/td][td]

555-1515​

[/td][td]

555-2222​

[/td][td]

1167 Rockport Dr.​

[/td][/tr]

[tr][td]

9​

[/td][td]

Capstone​

[/td][td]

Phil Caporal​

[/td][td]

CEO​

[/td][td]

555-6881​

[/td][td]

555-6666​

[/td][td]

3455 Delgado Dr.​

[/td][/tr]

[tr][td]

10​

[/td][td]

ABC Co.​

[/td][td]

David Loy​

[/td][td]

Sales Manager​

[/td][td]

555-5058​

[/td][td]

555-0000​

[/td][td]

8010 Winchester Ln.​

[/td][/tr]

[tr][td]

11​

[/td][td]

Delta​

[/td][td]

Patricia White​

[/td][td]

VP of Sales​

[/td][td]

555-5077​

[/td][td]

555-2222​

[/td][td]

9631 Express Ct.​

[/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

Company​

[/td][td]

Company #​

[/td][td]

Company Address​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]ABC Co.[/td][td]555-0000[/td][td]123 Main St.[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]Delta[/td][td]555-2222[/td][td]100 McKinney Ln.[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]Capstone[/td][td]555-6666[/td][td]650 Kings Ct.[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Make sure your headings in both tables match, it makes this much easier.

A15=IFERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($A$14:A14,$A$2:$A$11),),0)),"")
copied down as needed
B15=IF($A15="","",INDEX($B$2:$K$11,MATCH($A15,$A$2:$A$11,0),MATCH(B$14,$B$1:$K$1,0)))
copied down and across

 

[wesleyterrill]

Thanks for the responses, guys! Ford is right, the remove duplicates isn't an option for what I'm trying to do. I will eventually put a job aid together for some coworkers - i.e. the less steps/more automated processes, the better!

Ford, I think you're on the right track. How would your formulae change if we wanted that information on the next sheet? So instead of indexing from A2:A11, we would index from Sheet1!A2:A11, does that make sense? The initial data I'm working with is very extensive, it would be easier if our "refinement" were on another sheet (especially for the other people who will be using this template and aren't nearly as Excel savvy.)

Thanks again!

 

[FDibbins]

You would just need to include the sheet address. An easy way to do that, is to just MOVE that 2nd table to a new sheet and see how the syntax changes...
On sheet2...
A2=IFERROR(INDEX(Sheet1!$A$2:$A$11,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$11),),0)),"")
B2=IF($A2="","",INDEX(Sheet1!$B$2:$K$11,MATCH($A2,Sheet1!$A$2:$A$11,0),MATCH(B$1,Sheet1!$B$1:$K$1,0)))

 

[wesleyterrill]

Thanks for the help, Ford.

Your formula for A2 works flawlessly. But when I use your B2 formula (on either sheet), I get a "#N/A" as the output. Any ideas?

 

[wesleyterrill]

I ended up using Ford's formula for A2 [=IFERROR(INDEX(Sheet1!$A$2:$A$11,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$11),),0)),"")]. For the rest of the data, I just used the VLOOKUP function.

On Sheet 2, I used the formula [=VLOOKUP(A2,Sheet1!A2:Sheet1!F11,5,FALSE). Not exactly what I was imagining originally in my head but it gets the job done! I guess that's the magic with Excel. Thanks for the help guys, cheers!