transpose data excluding some columns and rows

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
I receive data in a worksheet that contains items in rows across multiple columns. I want to transpose this data so that the items are contained in columns with multiple rows. I can do the “copy > paste special > transpose” but I get the same worksheet sent to me each week with additional data, and don’t want to copy/paste every time. Plus, there are some columns that I do not want to transpose and also some blank rows that I also do not want.

Does anyone have any ideas on how I can “convert” this data?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
34sweetness,

Some (little) sample would help to see the problem for everybody here...

post for instance:

Main Sheet BEFORE
DATA YOU RECIEVE
what you want to happen
Main Sheet AFTER

kind regards,
Erik
 

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
Okay, here is an example of the worksheet I received updated each week:
Example.xls
ABCDEFGHIJKLM
1
2Basic PartsWidgitsJanuary 2005February 2005
37-Jan14-Jan21-Jan28-Jan4-Feb11-Feb18-Feb25-Feb
4M725013Small243255242234213248233
5M725015Medium198279141246223170187
6M725020Large224190139192183189216
7M725022X-Large294271210428313363183
89599957321100378693297081902721
9
10WindingsJanuary 2005February 2005
117-Jan14-Jan21-Jan28-Jan4-Feb11-Feb18-Feb25-Feb
12M725049Round226174174162179135197
13M725052Square12221262878
142381961861687882071422050554
15
Sheet1
 

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
And this is what I would like to "Transpose" the data into:
Example.xls
ABCD
1DateGroupPart NumberCases Shipped
27-JanWidgitM725013243
37-JanWidgitM725015198
47-JanWidgitM725020224
57-JanWidgitM725022294
67-JanWindingM725049226
77-JanWindingM72505212
814-JanWidgitM725013255
914-JanWidgitM725015279
1014-JanWidgitM725020190
1114-JanWidgitM725022271
1214-JanWindingM725049174
1314-JanWindingM72505222
1421-JanWidgitM725013242
1521-JanWidgitM725015141
1621-JanWidgitM725020139
1721-JanWidgitM725022210
1821-JanWindingM725049174
1921-JanWindingM72505212
2028-JanWidgitM725013234
2128-JanWidgitM725015246
2228-JanWidgitM725020192
2328-JanWidgitM725022428
2428-JanWindingM725049162
2528-JanWindingM7250526
264-FebWidgitM725013213
274-FebWidgitM725015223
284-FebWidgitM725020183
294-FebWidgitM725022313
304-FebWindingM725049179
Sheet3
 

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
Any Ideas?

I would like to set this up so that it can be easily "updated" with the new data I receive each week.

I am sent the workbook with the updated info.

Note: some months contain 4 weeks of data and some contain 5 weeks of data. It just depends on the month...
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
34sweetness,

To be honnest, I don't like this one.
Yhe layout to start with is difficult to implement.

Will you copy only one column a week to add at the bottom of a list?
How many basic parts do you have?

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top