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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
146
ED38
E
Cubist
  • Solved
Expand each row n number of times.
2
Replies
16
Views
644
Cubist
Cubist
S
  • Solved
Simple nesting issue
Replies
3
Views
421
Fluff
Fluff
W
  • Solved
Index/Match Cannot The Find Column
2
Replies
11
Views
1K
Peter_SSs
Peter_SSs
icytuvi
  • Solved
Join text in excel
2
Replies
15
Views
647
Burrgogi
Burrgogi

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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