Simple (?) chart prob--Needing a simple solution!

**baloo308** · 10-06-2016, 10:46 AM

Hello all,

I apologize in advance for asking a question that has likely been answered before--I am in a new job that requires a good bit of Excel work, and I am not familiar with advanced functions/terms at all so I'm hoping someone can point me in a simple direction. I commonly create spreadsheets that pull from a massive database to list people's contact information. If someone has multiple phone numbers saved in our database, he or she will show up on multiple rows, with one row per unique phone number. I want to find a way to condense the multi-line people into one row each by spreading out their phone numbers into additional "phone" columns when needed. Everything I find on Google relates to removing duplicates instead, but each row has different phone numbers so I don't want to lose any. I also have other info like addresses on these lines, and many hundreds of names, that are complicating my efforts. I am attaching a sample screenshot that shows what I'm hoping to do in a simplified format. Any suggestions, keeping in mind I am not very knowledgeable?Excel.jpg

**Glenn Kennedy** · 10-06-2016, 12:24 PM

OK. I have used 3 formulae here to get your desired result.
1. To return a list of names (array formula), copied down:
=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($G$1:$G1,$A$2:$A$15),0),0)),"")

2. To return addresses (copied down):
=IFERROR(INDEX($B$2:$B$15,MATCH(G2,$A$2:$A$15,0)),"")

3. To return the 'phone numbers (array formula, copied across and down):

Formula:

=IFERROR(IFERROR(INDEX($C:$C,SMALL(IF(($A$2:$A$15=$G2)*($C$2:$C$15<>""),ROW($A$2:$A$15)),COLUMNS($A:A))),INDEX($D:$D,SMALL(IF(($A$2:$A$15=$G2)*($D$2:$D$15<>""),ROW($A$2:$A$15)),COLUMNS($A:A)-(COUNTIFS($A:$A,$G2,$B:$B,$H2,$C:$C,"<>"""))))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...