Excel -- Cell Values Match in First Row, Return Values on One Row with Multiple Columns

Question

Here's an explanation of what I'm having difficulty with:

Column A: Lists the Address Book Numbers for different companies (1234, 1235, 1236, etc.)
Column B: Lists the Phone Types (Cell, Fax, Home)
Column C: Lists Company Phone Numbers

Address Book Numbers from Column A are repeated in multiple rows (due to the fact that some companies have more than one Phone Number in Column C)... So, I'm looking to consolidate the Address Book Numbers that are the same (to one row) and have each phone number pertaining to the same address number in additional columns (within that row).

---

Current Excel Table:

---

AddressBookNumber  PhoneType  PhoneNumber
1234               CELL       (444)444-4444
1235               FAX        (777)777-7777
1234               OFFICE     (000)000-0000
1236               FAX        (222)222-2222
1234               HOME       (555)555-5555
1236               OFFICE     (111)111-1111

---

Would like my Excel Table to look like:

|AddressBookNumber | PhoneType1 | PhoneNumber1  | Phone Type2 | PhoneNumber2  | PhoneType3 | PhoneNumber3  |
|1234              |CELL        | (444)444-4444 | OFFICE      | (000)000-0000 | HOME       | (555)555-5555 |
|1235              |FAX         | (777)777-7777 |             |               |            |
|1236              |FAX         | (222)222-2222 | OFFICE      | (111)111-1111 |            |

---

Essentially, I need to have the phone numbers pertaining to one company all in one row.. would appreciate any assistance on the formula I should use. Thanks!

Answer 1

To get the unique list use this array formula put this in F2:

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$7),0)),"")

Hit Ctrl-Shift-Enter on exit of edit mode instead of Enter. Then copy/drag down till you get blanks.

And to get the numbers put this in G2:

=IFERROR(INDEX($B$2:$C$7,MATCH(1,(COUNTIFS($E$2:E2,$B$2:$B$7,$F$2:F2,$C$2:$C$7)=0)*($A$2:$A$7=$F2),0),MOD(COLUMN(A:A)-1,2)+1),"")

Hit Ctrl-Shift-Enter on exit of edit mode instead of Enter. Then copy/drag down and over till you get blanks.