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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

M
  • Solved
How to get multiple outputs from XLOOKUP?
Replies
2
Views
656
mwl_y
M
G
  • Question Question
Blood pressure data summary Pivot Table - unable to create the desired result
Replies
3
Views
418
awoohaw
awoohaw
W
  • Solved
Nested Xlookups? - Can't format data to make multiple criteria give desired output
Replies
3
Views
399
Eric W
Eric W
J
  • Question Question
Update Power Query through VBA
Replies
0
Views
85
johnny51981
J
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
426
Fluff
Fluff

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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