How to transpose(?) column to desired output

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
try PowerQuery aka Get&Transform:

Column1Custom.1Custom.2Custom.3Custom.4Custom.5Custom.6Custom.7Custom.8
INVINV
268385​
29/05/2019​
JOB: T00009719
28/06/2019​
1394.52​
1394.52​
1394.52​
268385​
INV
268466​
29/05/2019​
JOB: T00009709
28/06/2019​
1947.08​
1947.08​
3341.6​
29-May-19​
JOB: T00009719
28-Jun-19​
1,394.52​
1,394.52​
1,394.52​
INV
268466​
29-May-19​
JOB: T00009709
28-Jun-19​
1,947.08​
1,947.08​
3,341.60​

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:

A
B
C
D
E
F
G
H
I
J
1
INVINV
268385​
29/05/2019​
JOB: T00009719
28/06/2019​
1394.52​
1394.52​
1394.52​
2
268385​
INV
268466​
29/05/2019​
JOB: T00009709
28/06/2019​
1947.08​
1947.08​
3341.6​
3
29/05/2019​
4
JOB: T00009719
5
28/06/2019​
6
1394.52​
7
1394.52​
8
1394.52​
9
INV
10
268466​
11
29/05/2019​
12
JOB: T00009709
13
28/06/2019​
14
1947.08​
15
1947.08​
16
3341.6​

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
1. Does every INV section contain exactly 8 rows like your samples?

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
.. not every INV section is exactly 8 rows.

Using formulas or Power Query would be the preferred method.
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
Here is a formula method that would cope with different size sections

Hi Peter,
Thank you for this awesome solution, I appreciate it :)
This is something I will be using many times a week. Simply amazing.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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