How to transpose only populated cells?

[couriol]

Hi,

I'm working in the fashion business

I have a large database with data at style level (gender, product type, price...) and quantities size by size (s, m, l, xl)

Typical data would be:
women, sweater, $30.00, 0, 10, 15, 13, 6, 0, 0 for the following column headers:
gender, product_type, price, size_xs, size_s, size_m, size_l, size_xl, size_xxl, size_xxxl

I would like to have one row for each size, excluding sizes with 0 units, and mentioning the appropriate size header
This would look like that
women, sweater, $30.00, size_s, 10
women, sweater, $30.00, size_m, 15
women, sweater, $30.00, size_l, 13
women, sweater, $30.00, size_xl, 6

Can anyone help?

Thanks a lot!!!

 

[hiker95]

couriol,

Welcome to the MrExcel forum.


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	gender	product_type	price	size_xs	size_s	size_m	size_l	size_xl	size_xxl	size_xxxl
2	women	sweater	$30.00	0	10	15	13	6	0	0
3
4
5
6
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	gender	product_type	price	size_xs	size_s	size_m	size_l	size_xl	size_xxl	size_xxxl		gender	product type	price	size	qty
2	women	sweater	$30.00	0	10	15	13	6	0	0		women	sweater	$30.00	size_s	10
3												women	sweater	$30.00	size_m	15
4												women	sweater	$30.00	size_l	13
5												women	sweater	$30.00	size_xl	6
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 12/27/2010
' http://www.mrexcel.com/forum/showthread.php?t=517677
Dim LR As Long, a As Long, b As Long, NR As Long, NC
Application.ScreenUpdating = False
Range("L1:P1") = [{"gender","product type","price","size","qty"}]
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To LR Step 1
  For b = 4 To 10 Step 1
    If Cells(a, b) <> 0 Then
      NR = Range("L" & Rows.Count).End(xlUp).Offset(1).Row
      Range("L" & NR).Resize(, 3).Value = Range("A" & a).Resize(, 3).Value
      Range("O" & NR).Value = Cells(1, b).Value
      Range("P" & NR).Value = Cells(a, b).Value
    End If
  Next b
Next a
Range("L1:P" & NR).Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[couriol]

Hi hiker95,
This is terrific !!!
Is there a way to create this new table into a new tab? Or best onto another existing tab?
Thanks again

 

[hiker95]

couriol,

Is my posted sample data correct (titles in the correct row and column)?

Is there a way to create this new table into a new tab? Or best onto another existing tab?

What is your choice?

If another existing tab, what is the worksheet name, and can I have a screenshot?

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste

 

[couriol]

Sorry but I can't post my data for confidentiality reasons, and I can't use Excel Jeanie (nor your other links) because I don't have admin rights on my office laptop.

Your posted sample data is PERFECT

I have a much larger database (more columns, more rows) but I think that I understand your macro and can adapt it to my needs.

What is unclear to me yet is how to create the new columns in a different tab.
I could use an existing blank tab ('Target Datasheet')

How would you change your macro for that?

 

[hiker95]

couriol,

What is unclear to me yet is how to create the new columns in a different tab.
I could use an existing blank tab ('Target Datasheet')

Can I see the structure (just the title row) of worksheet Target Datasheet?

You can upload a workbook containing just the titles in worksheet Target Datasheet to www.box.net and provide us with a link to your workbook.

 

[hiker95]

couriol,

Will the new data for worksheet Target Datasheet be copied to the next available blank row?

 

[couriol]

Hi,
I have copied a typical template in http://www.box.net/signup/collablink/d_62095510/7f2e9fd0b002f

Existing data is in Sheet1. I would like to have the new data in Sheet2.

Thanks!

 

[hiker95]

couriol,

Your link to box.net is asking for a userid and password.

Upload your workbook to www.box.net and provide us with a link to your workbook.

 

[hiker95]

couriol,

Existing data is in Sheet1. I would like to have the new data in Sheet2.

Sample data on worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	gender	product_type	price	size_xs	size_s	size_m	size_l	size_xl	size_xxl	size_xxxl
2	women	sweater	$30.00	0	10	15	13	6	0	0
3
Sheet1

After the current macro on worksheet Sheet2:

Excel Workbook
	A	B	C	D	E
1	gender	product type	price	size	qty
2	women	sweater	$30.00	size_s	10
3	women	sweater	$30.00	size_m	15
4	women	sweater	$30.00	size_l	13
5	women	sweater	$30.00	size_xl	6
6
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV2()
' hiker95, 12/28/2010
' http://www.mrexcel.com/forum/showthread.php?t=517677
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, b As Long, NR As Long, NC
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Sheet2!A1)") Then Worksheets.Add(After:=w1).Name = "Sheet2"
Set w2 = Worksheets("Sheet2")
w2.Range("A1:E1") = [{"gender","product type","price","size","qty"}]
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To LR Step 1
  For b = 4 To 10 Step 1
    If w1.Cells(a, b) <> 0 Then
      NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      w2.Range("A" & NR).Resize(, 3).Value = w1.Range("A" & a).Resize(, 3).Value
      w2.Range("D" & NR).Value = w1.Cells(1, b).Value
      w2.Range("E" & NR).Value = w1.Cells(a, b).Value
    End If
  Next b
Next a
w2.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.