Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-10-2017, 03:30 PM
dealta dealta is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Aug 2017
Posts: 1
dealta is on a distinguished road
Default Transpose & Consolidate

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
File Type: xlsx TransposeAndConsolidateQuestion_081017.xlsx (20.7 KB, 3 views)
Reply With Quote
  #2  
Old 08-11-2017, 07:27 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 388
NoSparks is on a distinguished road
Default

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 ?
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidate 25 Macros Into One, Too Many Hot Keys! StephenRay Word VBA 6 08-04-2017 05:52 AM
Consolidate data from different excel sheets Sandhya Excel Programming 1 10-10-2015 12:28 AM
Consolidate Pivots linked to SQL mtayloripf Excel 0 02-11-2014 08:16 AM
Consolidate names jgross30 Excel 7 05-13-2013 08:36 PM
Transpose cells and skip? ItsjustB Excel 1 01-09-2013 08:53 AM


All times are GMT -7. The time now is 09:49 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft