ExcelBanter - values in columns where email address is the same

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 fail any of those tests.

HTH,
Bernie
MS Excel MVP

"SandyUK" wrote in message
...

425
569
122
122
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
425 569 122
122
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.

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