How to transpose(?) column to desired output

D

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows

Book1
ABCDEFGH
1INV
2268385
329-May-19
4JOB: T00009719
528-Jun-19
61,394.52
71,394.52
81,394.52
9INV
10268466
1129-May-19
12JOB: T00009709
1328-Jun-19
141,947.08
151,947.08
163,341.60
17
18INV26838529-May-19JOB: T0000971928-Jun-191,394.521,394.521,394.52
19INV26846629-May-19JOB: T0000970928-Jun-191,947.081,947.083,341.60
Sheet1


Need to get the column into desired output.
FWIW It doesn't matter how many rows in between the data in the desired output.
As long as I can get one invoices details into one row, I am happy.
How best can I get about this?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
try PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][td=bgcolor:#70AD47]Custom.4[/td][td=bgcolor:#70AD47]Custom.5[/td][td=bgcolor:#70AD47]Custom.6[/td][td=bgcolor:#70AD47]Custom.7[/td][td=bgcolor:#70AD47]Custom.8[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]INV[/td][td][/td][td=bgcolor:#E2EFDA]INV[/td][td=bgcolor:#E2EFDA]
268385​
[/td][td=bgcolor:#E2EFDA]
29/05/2019​
[/td][td=bgcolor:#E2EFDA]JOB: T00009719[/td][td=bgcolor:#E2EFDA]
28/06/2019​
[/td][td=bgcolor:#E2EFDA]
1394.52​
[/td][td=bgcolor:#E2EFDA]
1394.52​
[/td][td=bgcolor:#E2EFDA]
1394.52​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
268385​
[/td][td][/td][td]INV[/td][td]
268466​
[/td][td]
29/05/2019​
[/td][td]JOB: T00009709[/td][td]
28/06/2019​
[/td][td]
1947.08​
[/td][td]
1947.08​
[/td][td]
3341.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
29-May-19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]JOB: T00009719[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
28-Jun-19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1,394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1,394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1,394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]INV[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
268466​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
29-May-19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]JOB: T00009709[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
28-Jun-19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1,947.08​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1,947.08​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3,341.60​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Index = Table.AddIndexColumn(TypeText, "Index", 0, 1),
    Divide = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, 8), Int64.Type}}),
    Group = Table.Group(Divide, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8"}),
    TypeNumber = Table.TransformColumnTypes(Split,{{"Custom.2", type number}, {"Custom.6", type number}, {"Custom.7", type number}, {"Custom.8", type number}, {"Custom.3", type datetime}, {"Custom.5", type datetime}}),
    TypeDate = Table.TransformColumnTypes(TypeNumber,{{"Custom.3", type date}, {"Custom.5", type date}}),
    ROC = Table.SelectColumns(TypeDate,{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8"})
in
    ROC[/SIZE]
 
Upvote 0
If the previous post has not resolved your issue ..

1. Does every INV section contain exactly 8 rows like your samples?

2. Are you looking for a particular approach (eg Formulas, Macro, PowerQuery)?

3. About how big is the data likely to be (that is, total rows in the original data)?
 
Upvote 0
with formula:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
1​
[/td][td]INV[/td][td][/td][td]INV[/td][td]
268385​
[/td][td]
29/05/2019​
[/td][td]JOB: T00009719[/td][td]
28/06/2019​
[/td][td]
1394.52​
[/td][td]
1394.52​
[/td][td]
1394.52​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
2​
[/td][td]
268385​
[/td][td][/td][td]INV[/td][td]
268466​
[/td][td]
29/05/2019​
[/td][td]JOB: T00009709[/td][td]
28/06/2019​
[/td][td]
1947.08​
[/td][td]
1947.08​
[/td][td]
3341.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
3​
[/td][td]
29/05/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
4​
[/td][td]JOB: T00009719[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
5​
[/td][td]
28/06/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
6​
[/td][td]
1394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
7​
[/td][td]
1394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
8​
[/td][td]
1394.52​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
9​
[/td][td]INV[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
10​
[/td][td]
268466​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
11​
[/td][td]
29/05/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
12​
[/td][td]JOB: T00009709[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
13​
[/td][td]
28/06/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
14​
[/td][td]
1947.08​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
15​
[/td][td]
1947.08​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
16​
[/td][td]
3341.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

C1: =OFFSET($A$1,(8*(ROW()-ROW(C$1)))+(COLUMN()-COLUMN($C1)),0,1,1)
drag 8 to the right / and in this case 2 down
 
Upvote 0
or formula: =IF((8*(ROW()-ROW(L$1)))+(COLUMN()-COLUMN($L1))>=ROWS($A$1:$A$16),"",OFFSET($A$1,(8*(ROW()-ROW(L$1)))+(COLUMN()-COLUMN($L1)),0,1,1))
can be used when the number of elements in the source column is variable but some maximum number of elements is known.
 
Upvote 0
Peter_SSs said:
1. Does every INV section contain exactly 8 rows like your samples?
Click to expand...

Hi Peter,
I just had a look at the data and not every INV section is exactly 8 rows. Most of them are 8 rows but there a handful that are 9 or 10 rows.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
https://1drv.ms/x/s!AvjBsEPEq12ngSf1Y0NtNKb5St6R?e=gMv2u3
​
[/FONT]
FWIW this is the PDF of the data I copied and pasted to Excel: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://ibb.co/RNBPczT
I suppose I could copy/paste the data which produce 8 rows (and use Sandy's formulas) and manually work on the 9 or 10 row producing entries.
[/FONT]
Final output is about 60 rows.
The 'raw' data of one column has about 400 rows.

Using formulas or Power Query would be the preferred method.
 
Upvote 0
danhendo888 said:
.. not every INV section is exactly 8 rows.

Using formulas or Power Query would be the preferred method.
Click to expand...
Here is a formula method that would cope with different size sections.
To keep the formulas a bit shorter I have used two helper columns (B:C) which could be hidden once populated.
Formulas in B1, C1 & D1 copied down as far as you might ever need (after adjusting the $400 if needed)
Formula in E1 is copied across as far as you might ever need and down as far as the earlier formulas.
You may need to format the date and number columns appropriately after copying the formulas.

Excel Workbook
ABCDEFGHIJKLMN
1INV18INV26838529-May-19JOB: T0000971928-Jun-191,394.521,394.521,394.52
226838599INV26846629-May-19JOB: T0000970928-Jun-191,947.081,947.083,341.605,698.00
329-May-19187INV26846730-May-19JOB: T000097109-Jun-192,365.22666.66
4JOB: T00009719
528-Jun-19
61,394.52
71,394.52
81,394.52
9INV
10268466
1129-May-19
12JOB: T00009709
1328-Jun-19
141,947.08
151,947.08
163,341.60
175,698.00
18INV
19268467
2030-May-19
21JOB: T00009710
229-Jun-19
232365.22
24666.66
25
Transpose
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Construction example data - need to split data from columns into rows
Replies
2
Views
119
HighAndWilder
H
M
  • Solved
How to get multiple outputs from XLOOKUP?
Replies
2
Views
829
mwl_y
M
W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
79
Peter_SSs
Peter_SSs
G
  • Question Question
Blood pressure data summary Pivot Table - unable to create the desired result
Replies
3
Views
457
awoohaw
awoohaw
W
  • Solved
Nested Xlookups? - Can't format data to make multiple criteria give desired output
Replies
3
Views
408
Eric W
Eric W

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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