values in columns where email address is the same

[SandyUK]

[email protected] 425
[email protected] 569
[email protected] 122
[email protected] 122
[email protected] 501

I get information in a format similar to this from a web enquiry form and I am looking to convert it to the format below i.e. 1 instance of each unique email address but if they have enquired about multiple products it is reflected.

Product1 Product2 Product3
[email protected] 425 569 122
[email protected] 122
[email protected] 501

Can anybody point me in the right direction?

As an aside I am looking to clean up the email addresses as well because there are some instances where they are blatently wrong e.g. [email protected].u or [email protected]

Any help appreciated

Adrian

[Bernie Deitrick]

Adrian,

If you use a formula in another column - let's say that your date starts in A2, and you have values
in column B, then in C2, use the formula

="Product" & COUNTIF($A$2:A2,A2)

and copy down to match your data set.

Then use a pivot table, with the emails as the row data, the second column as the data item, and the
third column as the column field.

As for your examples of bad email addresses, they are actually valid. Here are the rules:

Use only alphanumeric characters. That is, use only "A" through "Z" ("a" through "z")and "0 (zero)"
through "9".
Do not use the following characters: < > ( ) [ ] ; : , @ \
As long as they are not the first character in the email address, hyphens ( - ), underscores ( _ ),
periods ( . ), and numeric characters ("0" through "9") are acceptable characters to use within the
address.
Do not use null characters (spaces) between characters.

So, neither [email protected].u nor [email protected] fail any of those tests.


HTH,
Bernie
MS Excel MVP


"SandyUK" <[email protected]> wrote in message
news:[email protected]...
>
> [email protected] 425
> [email protected] 569
> [email protected] 122
> [email protected] 122
> [email protected] 501
>
> I get information in a format similar to this from a web enquiry form
> and I am looking to convert it to the format below i.e. 1 instance of
> each unique email address but if they have enquired about multiple
> products it is reflected.
>
> Product1 Product2 Product3
> [email protected] 425 569 122
> [email protected] 122
> [email protected] 501
>
> Can anybody point me in the right direction?
>
> As an aside I am looking to clean up the email addresses as well
> because there are some instances where they are blatently wrong e.g.
> [email protected].u or [email protected]
>
> Any help appreciated
>
> Adrian
>
>
> --
> SandyUK
> ------------------------------------------------------------------------
> SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
> View this thread: http://www.excelforum.com/showthread...hreadid=563199
>

[SandyUK]

Thanks for the response it has given me a good start on achieving what i am looking for.

Much appreciated

Adrian