pasted data from a pivot table transposed to two columns

[C Goody]

Hi There, please can someone put me out of my misery as I am scratching my head on this.

I was sent a worksheet that was basically pasted values from a pivot table, the problem I have is that the header and subheading are duplicating the qty values and i need to display them differently or transpose them

example here

PRODUCT	QTY
PRODUCT A	3
product a description	3
PRODUCT B	1
product b description	1
PRODUCT C	1
product c description	1
PRODUCT D	1
product d description	1
PRODUCT E	1
product e description	1
PRODUCT F	9
product f description	9
PRODUCT G	33
product g description	33
PRODUCT H	1
product h description	1
PRODUCT I	2
product i description	2
PRODUCT J	2
product j description	2
PRODUCT K	2
product k description	2
PRODUCT L	4
product l description	4

<colgroup><col><col></colgroup><tbody>
</tbody>

I need to display the product title and description in adjacent cells as below

PRODUCT	PRODUCT DESCRIPTION	QTY
PRODUCT A	product a description	3
PRODUCT B	product b description	1
PRODUCT C	product c description	1
PRODUCT D	product d description	1
PRODUCT E	product e description	1
PRODUCT F	product f description	9
PRODUCT G	product g description	33
PRODUCT H	product h description	1
PRODUCT I	product i description	2
PRODUCT J	product j description	2
PRODUCT K	product k description	2
PRODUCT L	product l description	4

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Has anyone got any suggestions that may help, I feel it may not be as simple as I am thinking and may need some VBA coding

thanks for the help

 

[sheetspread]

This can be done easily with formulas:

Excel 2010
	A	B	C	D
1	PRODUCT	QTY
2	PRODUCT A	3	product a description	0
3	product a description	3	PRODUCT B	1
4	PRODUCT B	1	product b description	0
5	product b description	1	PRODUCT C	1
6	PRODUCT C	1	product c description	0
7	product c description	1	PRODUCT D	1
8	PRODUCT D	1	product d description	0
9	product d description	1	PRODUCT E	1
10	PRODUCT E	1	product e description	0
11	product e description	1	PRODUCT F	1
12	PRODUCT F	9	product f description	0
Sheet5 (2)

Cell Formulas
Range	Formula
C2	=A3
D2	=MOD(ROW(A2),2)

then sort.

(If you actually have the word "description" or some other consistent term, ctrl-h with a wildcard to blank them out, then F5 and delete the rows)

 

[C Goody]

Perfect. thanks for that, often the easiest of solutions we tend to overthink

much appreciated

This can be done easily with formulas:

Excel 2010

	A	B	C	D
1	PRODUCT	QTY
2	PRODUCT A	3	product a description	0
3	product a description	3	PRODUCT B	1
4	PRODUCT B	1	product b description	0
5	product b description	1	PRODUCT C	1
6	PRODUCT C	1	product c description	0
7	product c description	1	PRODUCT D	1
8	PRODUCT D	1	product d description	0
9	product d description	1	PRODUCT E	1
10	PRODUCT E	1	product e description	0
11	product e description	1	PRODUCT F	1
12	PRODUCT F	9	product f description	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5 (2)​

Worksheet Formulas Cell Formula C2 =A3 D2 =MOD(ROW(A2),2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

then sort.

(If you actually have the word "description" or some other consistent term, ctrl-h with a wildcard to blank them out, then F5 and delete the rows)