RaqForum 28 No.
374 View  • 
     ...

Move Cells Containing Values in the Last Four Columns into Corresponding Rows

 
row-to-column transposition(2)

Problem description & analysis

We have an Excel file Book1.xlsx. Below is data in sheet1:

A

B

C

D

E

F

1

Color

Food

1-Jan

1-Feb

1-Mar

1-Apr

2

Red

Coke

3

4

5

3

Purple

Grapes

6

3

7

4

Green

Grapes

1

1

1

5

Yellow

Lemon

2

3

6

Orange

Orange

1

2

7

Purple

Grapes

2

8

Purple

Grapes

3

9

Purple

Grapes

4

10

Purple

Grapes

5

We are trying to move cells that contain values under the last four columns to corresponding rows in sheet2. Below is the desired result:

A

B

C

D

1

Color

Food

Date

Quantity

2

Red

Coke

1-Jan

3

3

Red

Coke

1-Feb

4

4

Red

Coke

1-Mar

5

5

Purple

Grapes

1-Feb

6

6

Purple

Grapes

1-Mar

3

7

Purple

Grapes

1-Apr

7

8

Green

Grapes

1-Jan

1

9

Green

Grapes

1-Feb

1

10

Green

Grapes

1-Mar

1

11

Yellow

Lemon

1-Mar

2

12

Yellow

Lemon

1-Apr

3

13

Orange

Orange

1-Jan

1

14

Orange

Orange

1-Feb

2

15

Purple

Grapes

1-Jan

2

16

Purple

Grapes

1-Feb

3

17

Purple

Grapes

1-Mar

4

18

Purple

Grapes

1-Apr

5

Solution & explanation

First, we load add-in ExcelRaq.xll and macro file esproc_template.xla to Excel.

Select cell A1 in sheet2 and enter the following formula in it: =esproc("=?1.news(~.to(3:);?1.~(1):Color,?1.~(2):Food,?2(#):Date,~:Quantity).select(Quantity)",Sheet1!A2:F10,Sheet1!C1:F1), press Ctrl+Enter, and delete the first row to get results.

Q & A Collection

https://stackoverflow.com/questions/63601754/convert-array-to-list-and-copy-rows-that-are-on-the-same-row-vba


row-to-column transposition(2)
 
 •  374 View  •