pasted data from a pivot table transposed to two columns

C

C Goody

New Member
Joined
Oct 11, 2013
Messages
13
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

[TABLE="width: 278"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]PRODUCT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]PRODUCT A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]product a description[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]PRODUCT B[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]product b description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT C[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]product c description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT D[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]product d description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT E[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]product e description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT F[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]product f description[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]PRODUCT G[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]product g description[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]PRODUCT H[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]product h description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT I[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]product i description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT J[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]product j description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT K[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]product k description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT L[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]product l description[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 384"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]PRODUCT[/TD]
[TD]PRODUCT DESCRIPTION[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]PRODUCT A[/TD]
[TD]product a description[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]PRODUCT B[/TD]
[TD]product b description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT C[/TD]
[TD]product c description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT D[/TD]
[TD]product d description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT E[/TD]
[TD]product e description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT F[/TD]
[TD]product f description[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]PRODUCT G[/TD]
[TD]product g description[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]PRODUCT H[/TD]
[TD]product h description[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCT I[/TD]
[TD]product i description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT J[/TD]
[TD]product j description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT K[/TD]
[TD]product k description[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PRODUCT L[/TD]
[TD]product l description[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
This can be done easily with formulas:


Excel 2010
ABCD
1PRODUCTQTY
2PRODUCT A3product a description0
3product a description3PRODUCT B1
4PRODUCT B1product b description0
5product b description1PRODUCT C1
6PRODUCT C1product c description0
7product c description1PRODUCT D1
8PRODUCT D1product d description0
9product d description1PRODUCT E1
10PRODUCT E1product e description0
11product e description1PRODUCT F1
12PRODUCT F9product f description0
Sheet5 (2)
Cell Formulas
RangeFormula
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)
 
Upvote 0
Perfect. thanks for that, often the easiest of solutions we tend to overthink

much appreciated


sheetspread said:
This can be done easily with formulas:

Excel 2010
ABCD
PRODUCTQTY
PRODUCT Aproduct a description
product a descriptionPRODUCT B
PRODUCT Bproduct b description
product b descriptionPRODUCT C
PRODUCT Cproduct c description
product c descriptionPRODUCT D
PRODUCT Dproduct d description
product d descriptionPRODUCT E
PRODUCT Eproduct e description
product e descriptionPRODUCT F
PRODUCT Fproduct f description

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]0[/TD]

</tbody>
Sheet5 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=A3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=MOD(ROW(A2),2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Looking for a spilled formula returning the value in a table matching 3 conditions
Replies
5
Views
149
ED38
E
Cubist
  • Solved
Expand each row n number of times.
2
Replies
16
Views
657
Cubist
Cubist
S
  • Question Question
Shifting cell reference
Replies
5
Views
113
Cubist
Cubist
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
175
FormulasOnly
F

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top