Hi Folks,

I'm a basic Excel 2013 user. I have mailing lists for which I need to transpose several columns and consolidate others. If I'm able to attach the excel file, it will show you, with the two tabs, an example of what I have and what I want.

The files I work with have anywhere from 3k to 35k rows total with each buyer having anywhere from 1 to 9 product ids and 4 product types within each of those. So, my guess is I need a visual basic script to transform the files quickly and efficiently. My coding writing experience is with statistical analysis programs and it's been a while. However, I think if you give me a clear shell of a script I can fill in the holes. I be forever grateful for some guidance.

In case the sample file won't attach, below is a list of some of the pertinent fields and details:

schoolname
idnumber
orderid
productid
propertyshortdesc
retaildescription
memberfirstname
membermiddleinitial
memberlastname
memberfullname


addressline1
addressline2
addressline3
city
state
zip
country
email
phone

There are multiple rows for buyers and the number of rows varies depending on what the buyer ordered. In the original file, each row represents a combination of the below four fields for each buyer. I want to convert that information to a single row for each buyer showing all their orders.

I want to transpose the below fields for each idnumber.

orderid
productid
propertyshortdesc
retaildescription

and consolidate the below for each idnumber.


schoolname
memberfirstname
membermiddleinitial
memberlastname
memberfullname
addressline1
addressline2
addressline3
city
state
zip
country
email
phone

Thank you for any guidance you can provide. Please ask questions if I've left any needed info out.

Cheers,
dealta

Attached Files

TransposeAndConsolidateQuestion_081017.xlsx (20.7 KB, 10 views)

Can you elaborate on the 'Goal' sheet layout ?

I can understand 1 line per IDNumber but am at a loss regarding the OrderID.
Why space for 3 ProductID's for first 2 OrderID's then only 1 ProductID for the third OrderID ?

Is there a Product limit per Order and an Order limit per IDNumber ?
If not, I'd suggest NOT having the order information split the consolidated information.

What was [quote]each buyer having anywhere from 1 to 9 product ids and 4 product types within each of those[quote] supposed to lead us to ?

Do you have this request posted on any other forums ?