Find duplicates and move phone number to phone2

[tagteam]

I have a table that has a bunch of user names and emails. If the user has two emails then it shows them twice in the query. What is the best way with access/excel combo to get it to where will show the user once and put the phone number in cells like phone1 and phone2 etc.

I have tried a find duplicates query and that at least gives me all the users that have more than one phone but I have over 8k of them so I don't know any easy way to get it to one user with each email in a separate column.

Any help or ideas is appreciated.


Thanks

[June7]

Maybe a CROSSTAB query which would probably use DCount() domain aggregate function. Provide sample data as a table in post or attach db.

Or use VBA to concatenate phone numbers to a single string of data.

What do you mean by 'each email in a separate column' - the entire email message?

[Micron]

One possibility based on 'email' meaning email address - "user" details in main form, except perhaps email address and phone numbers. Then subform as datasheet or continuous, one record for each email. If there are 2 addresses and one phone, one phone field is blank in one of the records.

[tagteam]

Hey guys,
yes, email means email address. A bunch of users with the same email address have multiple phone numbers.

I ended up doing this with a several step method. I am sure there are better ways to do it but it worked and didn't take too long.
-Sort by email address
-Use countifs to get how many phone numbers each user/email has associated with it
-Copy and paste all the user/email with only 1 phone number to a new sheet and import that
-Copy and paste all the user/email with 2 phone numbers to a new sheet
-Use an if statement to label them as first or second
-Sort by label and then cut all the second and paste them in a new range next to the first ones. Then i can just delete the extra columns and I end up with FirstName, LastName, Email, Phone1/Phone2
-For the users with three emails I used an if statement to get the first and second ( like I did with the ones with two) and then in the column next to that I used an =if(And to label it as the third.
-Next I used concatenate to end up getting a single column that said First, Second, or SecondThird.
-next I did like before and sorted, cut, paste and remove unnecessary cells.

It actually worked pretty well and was pretty quick overall.