Transforming table, column manipulation, creating more lines that contain specific data

baldwinp · Oct 21, 2014

Hi I was hoping someone could help me come up with a formula or macro that will take data that looks like the top table and turn it into the bottom one. Thanks in advance.

[TABLE="width: 725"]
<TBODY>[TR]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Country[/TD]
[TD]Color 1[/TD]
[TD]Percent[/TD]
[TD]Color 2[/TD]
[TD]Percent[/TD]
[TD]Color 3[/TD]
[TD]Percent[/TD]
[TD]Letter Code[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4652[/TD]
[TD]US[/TD]
[TD]Blue[/TD]
[TD]0.7[/TD]
[TD]Pink[/TD]
[TD]0.2[/TD]
[TD]Orange[/TD]
[TD]0.1[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]Marry[/TD]
[TD]1861[/TD]
[TD]Brazil[/TD]
[TD]Green[/TD]
[TD]0.3[/TD]
[TD]Blue[/TD]
[TD]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD]bbb[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]1863[/TD]
[TD]Argentina [/TD]
[TD]Yellow[/TD]
[TD]0.5[/TD]
[TD]Green[/TD]
[TD]0.25[/TD]
[TD]Blue[/TD]
[TD]0.25[/TD]
[TD]ccc[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1564[/TD]
[TD]Germany[/TD]
[TD]Yellow[/TD]
[TD]0.5[/TD]
[TD]Blue[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]1562[/TD]
[TD]Egypt[/TD]
[TD]Pink[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ftr[/TD]
[/TR]
[TR]
[TD]Cathy[/TD]
[TD]4653[/TD]
[TD]China[/TD]
[TD]Yellow[/TD]
[TD]0.6[/TD]
[TD]Blue[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD]sad[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]7954[/TD]
[TD]Turkey[/TD]
[TD]Green[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]asd[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4787[/TD]
[TD]France[/TD]
[TD]Pink[/TD]
[TD]0.75[/TD]
[TD]Green[/TD]
[TD]0.15[/TD]
[TD]Yellow[/TD]
[TD]0.1[/TD]
[TD]ghf[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]161667[/TD]
[TD]UK[/TD]
[TD]Pink[/TD]
[TD]0.8[/TD]
[TD]Blue[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]dhd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Country[/TD]
[TD]Color[/TD]
[TD]Percent[/TD]
[TD]Letter Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4652[/TD]
[TD]US[/TD]
[TD]Blue[/TD]
[TD]0.7[/TD]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marry[/TD]
[TD]1861[/TD]
[TD]Brazil[/TD]
[TD]Green[/TD]
[TD]0.3[/TD]
[TD]bbb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]1863[/TD]
[TD]Argentina [/TD]
[TD]Yellow[/TD]
[TD]0.5[/TD]
[TD]ccc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1564[/TD]
[TD]Germany[/TD]
[TD]Yellow[/TD]
[TD]0.5[/TD]
[TD]def[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]1562[/TD]
[TD]Egypt[/TD]
[TD]Pink[/TD]
[TD]1[/TD]
[TD]ftr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cathy[/TD]
[TD]4653[/TD]
[TD]China[/TD]
[TD]Yellow[/TD]
[TD]0.6[/TD]
[TD]sad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]7954[/TD]
[TD]Turkey[/TD]
[TD]Green[/TD]
[TD]1[/TD]
[TD]asd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4787[/TD]
[TD]France[/TD]
[TD]Pink[/TD]
[TD]0.75[/TD]
[TD]ghf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]161667[/TD]
[TD]UK[/TD]
[TD]Pink[/TD]
[TD]0.8[/TD]
[TD]dhd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4652[/TD]
[TD]US[/TD]
[TD]Pink[/TD]
[TD]0.2[/TD]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marry[/TD]
[TD]1861[/TD]
[TD]Brazil[/TD]
[TD]Blue[/TD]
[TD]0.7[/TD]
[TD]bbb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]1863[/TD]
[TD]Argentina [/TD]
[TD]Green[/TD]
[TD]0.25[/TD]
[TD]ccc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1564[/TD]
[TD]Germany[/TD]
[TD]Blue[/TD]
[TD]0.5[/TD]
[TD]def[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cathy[/TD]
[TD]4653[/TD]
[TD]China[/TD]
[TD]Blue[/TD]
[TD]0.4[/TD]
[TD]sad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4787[/TD]
[TD]France[/TD]
[TD]Green[/TD]
[TD]0.15[/TD]
[TD]ghf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]161667[/TD]
[TD]UK[/TD]
[TD]Blue[/TD]
[TD]0.2[/TD]
[TD]dhd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4652[/TD]
[TD]US[/TD]
[TD]Orange[/TD]
[TD]0.1[/TD]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]1863[/TD]
[TD]Argentina [/TD]
[TD]Blue[/TD]
[TD]0.25[/TD]
[TD]ccc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]4787[/TD]
[TD]France[/TD]
[TD]Yellow[/TD]
[TD]0.1[/TD]
[TD]ghf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=2><COL span=2><COL span=2><COL></COLGROUP>[/TABLE]

hiker95 · Oct 21, 2014

baldwinp,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

The following macro using two arrays in memory should adjust for a varying number or rows, and, columns.

Sample raw data in the active worksheet:

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Name

Number

Country

Color 1

Percent

Color 2

Percent

Color 3

Percent

Letter Code

2

James

4652

US

Blue

0.7

Pink

0.2

Orange

0.1

aaa

3

Marry

1861

Brazil

Green

0.3

Blue

0.7

bbb

4

George

1863

Argentina

Yellow

0.5

Green

0.25

Blue

0.25

ccc

5

John

1564

Germany

Yellow

0.5

Blue

0.5

def

6

Tim

1562

Egypt

Pink

1

ftr

7

Cathy

4653

China

Yellow

0.6

Blue

0.4

sad

8

Tim

7954

Turkey

Green

1

asd

9

James

4787

France

Pink

0.75

Green

0.15

Yellow

0.1

ghf

10

Steve

161667

UK

Pink

0.8

Blue

0.2

dhd

11

12

13

14

15

16

17

18

19

20

21

Sheet1

After the macro in the same worksheet beginning in the third column after the last used column:

Excel 2007

M

N

O

P

Q

R

1

Name

Number

Country

Color

Percent

Letter Code

2

James

4652

US

Blue

0.7

aaa

3

Marry

1861

Brazil

Green

0.3

bbb

4

George

1863

Argentina

Yellow

0.5

ccc

5

John

1564

Germany

Yellow

0.5

def

6

Tim

1562

Egypt

Pink

1

ftr

7

Cathy

4653

China

Yellow

0.6

sad

8

Tim

7954

Turkey

Green

1

asd

9

James

4787

France

Pink

0.75

ghf

10

Steve

161667

UK

Pink

0.8

dhd

11

James

4652

US

Pink

0.2

aaa

12

Marry

1861

Brazil

Blue

0.7

bbb

13

George

1863

Argentina

Green

0.25

ccc

14

John

1564

Germany

Blue

0.5

def

15

Cathy

4653

China

Blue

0.4

sad

16

James

4787

France

Green

0.15

ghf

17

Steve

161667

UK

Blue

0.2

dhd

18

James

4652

US

Orange

0.1

aaa

19

George

1863

Argentina

Blue

0.25

ccc

20

James

4787

France

Yellow

0.1

ghf

21

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub ReorgData()
' hiker95, 10/21/2014, ME813049
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, luc As Long, c As Long, n As Long, mr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, 1).End(xlToRight).Column
a = Range(Cells(1, 1), Cells(lr, lc))
luc = Cells(1, Columns.Count).End(xlToLeft).Column
If luc > lc Then Columns(lc + 3).Resize(, luc - (lc + 2)).ClearContents
n = Application.CountIf(Rows(1), "Color*")
For c = 4 To lc - 2 Step 2
  mr = mr + Application.CountA(Range(Cells(2, c), Cells(lr, c)))
Next c
ReDim o(1 To mr, 1 To 6)
With Cells(1, lc + 3).Resize(, 6)
  .Value = Array("Name", "Number", "Country", "Color", "Percent", "Letter Code")
  .Font.Bold = True
End With
For c = 4 To lc - 2 Step 2
  For i = 2 To lr Step 1
    If Cells(i, c) <> "" Then
      j = j + 1
      o(j, 1) = a(i, 1)
      o(j, 2) = a(i, 2)
      o(j, 3) = a(i, 3)
      o(j, 4) = a(i, c)
      o(j, 5) = a(i, c + 1)
      o(j, 6) = a(i, lc)
    End If
  Next i
Next c
Cells(2, lc + 3).Resize(mr, 6).Value = o
Columns(lc + 3).Resize(, 6).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

Transforming table, column manipulation, creating more lines that contain specific data

baldwinp

New Member

hiker95

Well-known Member

Share this page

Transforming table, column manipulation, creating more lines that contain specific data

baldwinp

New Member

hiker95

Well-known Member

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock