Transpose set number of columns to rows after next blank column

seth333 · Aug 17, 2016

Hello,

I am new to this forum and was looking to see if anyone can assist me please.

I have a spreadsheet that has set of data which has 6 columns of data then a blank column and then again 6 columns and this spans across a lot of columns, every month a new set of 6 columns get added in. I want to move the set of 6 columns under the first set of 6 columns where that data stopped from first set of 6 columns data.

So looks like this ( blank column between Currency and OP column)

OP	Nom	Month	Year	Amount	Currency	OP	Nom	Month	Year	Amount	Currency	OP	Nom	Month	Year	Amount	Currency
C1	40000	Apr	2015	-203.49976	GBP	C1	40000	May	2015	-33312.05	GBP	C1	40000	Jun	2015	-38505.056	GBP
C2	40001	Apr	2015	0	GBP	C2	40001	May	2015	0	GBP	C2	40001	Jun	2015	0	GBP
C3	40002	Apr	2015	1375.3384	GBP	C3	40002	May	2015	0	GBP	C3	40002	Jun	2015	0	GBP
C4	40003	Apr	2015	0	GBP	C4	40003	May	2015	0	GBP	C4	40003	Jun	2015	0	GBP
C5	40004	Apr	2015	0	GBP	C5	40004	May	2015	0	GBP	C5	40004	Jun	2015	0	GBP
C6	40005	Apr	2015	0	GBP	C6	40005	May	2015	-12685.335	GBP	C6	40005	Jun	2015	-14662.848	GBP
C7	40006	Apr	2015	0	GBP	C7	40006	May	2015	-13389.888	GBP	C7	40006	Jun	2015	20044.945	GBP
C8	40007	Apr	2015	-12722.557	GBP	C8	40007	May	2015	21151.478	GBP	C8	40007	Jun	2015	24479.883	GBP
C9	40008	Apr	2015	-13429.177	GBP	C9	40008	May	2015	7272.5318	GBP	C9	40008	Jun	2015	7272.5318	GBP
C10	40009	Apr	2015	20587.797	GBP	C10	40009	May	2015	30701.3	GBP	C10	40009	Jun	2015	37528.3	GBP
C11	40010	Apr	2015	7272.5318	GBP	C11	40010	May	2015	921.96665	GBP	C11	40010	Jun	2015	1126.9829	GBP
C12	40011	Apr	2015	31549.626	GBP	C12	40011	May	2015	18.255857	GBP	C12	40011	Jun	2015	22.315383	GBP
C13	40012	Apr	2015	947.44204	GBP	C13	40012	May	2015	0	GBP	C13	40012	Jun	2015	0	GBP
C14	40013	Apr	2015	18.760295	GBP	C14	40013	May	2015	0	GBP	C14	40013	Jun	2015	0	GBP
C15	40014	Apr	2015	0	GBP	C15	40014	May	2015	0	GBP	C15	40014	Jun	2015	0	GBP
C16	40015	Apr	2015	0	GBP	C16	40015	May	2015	-4.1798404	GBP	C16	40015	Jun	2015	-5.1093049	GBP
C17	50000	Apr	2015	0	GBP	C17	50000	May	2015	-4.1798404	GBP	C17	50000	Jun	2015	-5.1093049	GBP
C18	50001	Apr	2015	-4.295336	GBP	C18	50001	May	2015	0	GBP	C18	50001	Jun	2015	0	GBP
C19	50002	Apr	2015	-4.295336	GBP	C19	50002	May	2015	0	GBP	C19	50002	Jun	2015	0	GBP
C20	50003	Apr	2015	0	GBP	C20	50003	May	2015	254.3747	GBP	C20	50003	Jun	2015	419.71826	GBP
C21	50004	Apr	2015	0	GBP	C21	50004	May	2015	-2937.0335	GBP	C21	50004	Jun	2015	-3449.7212	GBP
C22	50005	Apr	2015	254.3747	GBP	C22	50005	May	2015	0	GBP	C22	50005	Jun	2015	0	GBP
C23	50006	Apr	2015	-2952.1489	GBP	C23	50006	May	2015	-76.11966	GBP	C23	50006	Jun	2015	-89.407084	GBP
C24	50007	Apr	2015	0	GBP	C24	50007	May	2015	0	GBP	C24	50007	Jun	2015	0	GBP
C25	50008	Apr	2015	-76.511409	GBP	C25	50008	May	2015	-11.204885	GBP	C25	50008	Jun	2015	-13.160806	GBP
C26	50009	Apr	2015	0	GBP	C26	50009	May	2015	-203.49976	GBP	C26	50009	Jun	2015	-335.77461	GBP
C27	70000	Apr	2015	-11.262551	GBP	C27	70000	May	2015	0	GBP	C27	70000	Jun	2015	0	GBP
C28	70001	Apr	2015	0	GBP	C28	70001	May	2015	1368.2965	GBP	C28	70001	Jun	2015	1607.1459	GBP
C29	70002	Apr	2015	0	GBP	C29	70002	May	2015	-13432.184	GBP	C29	70002	Jun	2015	-15776.902	GBP
C30	70003	Apr	2015	0	GBP	C30	70003	May	2015	-6748.6115	GBP	C30	70003	Jun	2015	-7926.647	GBP
C31	70004	Apr	2015	-4.295336	GBP	C31	70004	May	2015	-3428.9983	GBP	C31	70004	Jun	2015	-3428.9983	GBP
C32	70005	Apr	2015	-4.295336	GBP	C32	70005	May	2015	0	GBP	C32	70005	Jun	2015	0	GBP
C33	70006	Apr	2015	0	GBP	C33	70006	May	2015	0	GBP	C33	70006	Jun	2015	0	GBP
C34	70007	Apr	2015	0	GBP	C34	70007	May	2015	-7.4644138	GBP	C34	70007	Jun	2015	-8.7673996	GBP
C35	70008	Apr	2015	254.3747	GBP	C35	70008	May	2015	0	GBP	C35	70008	Jun	2015	0	GBP
C36	70009	Apr	2015	-2952.1489	GBP	C36	70009	May	2015	0	GBP	C36	70009	Jun	2015	0	GBP
C37	70010	Apr	2015	0	GBP	C37	70010	May	2015	-2618.2324	GBP	C37	70010	Jun	2015	-3075.2703	GBP

and would like it to look like this

OP	Nom	Month	Year	Amount	Currency
C1	40000	Apr	2015	-203.49976	GBP
C2	40001	Apr	2015	0	GBP
C3	40002	Apr	2015	1375.3384	GBP
C4	40003	Apr	2015	0	GBP
C5	40004	Apr	2015	0	GBP
C6	40005	Apr	2015	0	GBP
C7	40006	Apr	2015	0	GBP
C8	40007	Apr	2015	-12722.557	GBP
C9	40008	Apr	2015	-13429.177	GBP
C10	40009	Apr	2015	20587.797	GBP
C11	40010	Apr	2015	7272.5318	GBP
C12	40011	Apr	2015	31549.626	GBP
C13	40012	Apr	2015	947.44204	GBP
C14	40013	Apr	2015	18.760295	GBP
C15	40014	Apr	2015	0	GBP
C16	40015	Apr	2015	0	GBP
C17	50000	Apr	2015	0	GBP
C18	50001	Apr	2015	-4.295336	GBP
C19	50002	Apr	2015	-4.295336	GBP
C20	50003	Apr	2015	0	GBP
C21	50004	Apr	2015	0	GBP
C22	50005	Apr	2015	254.3747	GBP
C23	50006	Apr	2015	-2952.1489	GBP
C24	50007	Apr	2015	0	GBP
C25	50008	Apr	2015	-76.511409	GBP
C26	50009	Apr	2015	0	GBP
C27	70000	Apr	2015	-11.262551	GBP
C28	70001	Apr	2015	0	GBP
C29	70002	Apr	2015	0	GBP
C30	70003	Apr	2015	0	GBP
C31	70004	Apr	2015	-4.295336	GBP
C32	70005	Apr	2015	-4.295336	GBP
C33	70006	Apr	2015	0	GBP
C34	70007	Apr	2015	0	GBP
C35	70008	Apr	2015	254.3747	GBP
C36	70009	Apr	2015	-2952.1489	GBP
C37	70010	Apr	2015	0	GBP
C1	40000	May	2015	-33312.05	GBP
C2	40001	May	2015	0	GBP
C3	40002	May	2015	0	GBP
C4	40003	May	2015	0	GBP
C5	40004	May	2015	0	GBP
C6	40005	May	2015	-12685.335	GBP
C7	40006	May	2015	-13389.888	GBP
C8	40007	May	2015	21151.478	GBP
C9	40008	May	2015	7272.5318	GBP
C10	40009	May	2015	30701.3	GBP
C11	40010	May	2015	921.96665	GBP
C12	40011	May	2015	18.255857	GBP
C13	40012	May	2015	0	GBP
C14	40013	May	2015	0	GBP
C15	40014	May	2015	0	GBP
C16	40015	May	2015	-4.1798404	GBP
C17	50000	May	2015	-4.1798404	GBP
C18	50001	May	2015	0	GBP
C19	50002	May	2015	0	GBP
C20	50003	May	2015	254.3747	GBP
C21	50004	May	2015	-2937.0335	GBP
C22	50005	May	2015	0	GBP
C23	50006	May	2015	-76.11966	GBP
C24	50007	May	2015	0	GBP
C25	50008	May	2015	-11.204885	GBP
C26	50009	May	2015	-203.49976	GBP
C27	70000	May	2015	0	GBP
C28	70001	May	2015	1368.2965	GBP
C29	70002	May	2015	-13432.184	GBP
C30	70003	May	2015	-6748.6115	GBP
C31	70004	May	2015	-3428.9983	GBP
C32	70005	May	2015	0	GBP
C33	70006	May	2015	0	GBP
C34	70007	May	2015	-7.4644138	GBP
C35	70008	May	2015	0	GBP
C36	70009	May	2015	0	GBP
C37	70010	May	2015	-2618.2324	GBP
C1	40000	Jun	2015	-38505.056	GBP
C2	40001	Jun	2015	0	GBP
C3	40002	Jun	2015	0	GBP
C4	40003	Jun	2015	0	GBP
C5	40004	Jun	2015	0	GBP
C6	40005	Jun	2015	-14662.848	GBP
C7	40006	Jun	2015	20044.945	GBP
C8	40007	Jun	2015	24479.883	GBP
C9	40008	Jun	2015	7272.5318	GBP
C10	40009	Jun	2015	37528.3	GBP
C11	40010	Jun	2015	1126.9829	GBP
C12	40011	Jun	2015	22.315383	GBP
C13	40012	Jun	2015	0	GBP
C14	40013	Jun	2015	0	GBP
C15	40014	Jun	2015	0	GBP
C16	40015	Jun	2015	-5.1093049	GBP
C17	50000	Jun	2015	-5.1093049	GBP
C18	50001	Jun	2015	0	GBP
C19	50002	Jun	2015	0	GBP
C20	50003	Jun	2015	419.71826	GBP
C21	50004	Jun	2015	-3449.7212	GBP
C22	50005	Jun	2015	0	GBP
C23	50006	Jun	2015	-89.407084	GBP
C24	50007	Jun	2015	0	GBP
C25	50008	Jun	2015	-13.160806	GBP
C26	50009	Jun	2015	-335.77461	GBP
C27	70000	Jun	2015	0	GBP
C28	70001	Jun	2015	1607.1459	GBP
C29	70002	Jun	2015	-15776.902	GBP
C30	70003	Jun	2015	-7926.647	GBP
C31	70004	Jun	2015	-3428.9983	GBP
C32	70005	Jun	2015	0	GBP
C33	70006	Jun	2015	0	GBP
C34	70007	Jun	2015	-8.7673996	GBP
C35	70008	Jun	2015	0	GBP
C36	70009	Jun	2015	0	GBP
C37	70010	Jun	2015	-3075.2703	GBP

thanks

Ombir · Aug 17, 2016

Seth, Try this for results in sheet2:

Code:

Sub Seth()
Dim i As Long
Dim rng As Range
Dim ar As Range
Dim Sh As Worksheet

Set Sh = Worksheets("Sheet2")

For Each ar In Sheets("Sheet1").Range("A1:ZZ1").SpecialCells(xlCellTypeConstants).Areas
    Set rng = ar.CurrentRegion
    rng.Copy Sh.Range("A1").Offset(i)
    i = i + rng.Rows.Count
Next
Sh.Range("A1").AutoFilter Field:=1, Criteria1:="OP"
Application.DisplayAlerts = False
Sh.AutoFilter.Range.Offset(1).Delete xlShiftUp
Application.DisplayAlerts = True
Sh.AutoFilterMode = False
End Sub

seth333 · Aug 17, 2016

amazing looks good , can i ask one more thing please if possible , each set of columns has a month is there a way to populate the blank space with year and 1 ( 1=month but starts from Apr )

so financial year and month would be
2015 and Apr=1 May=2 Jun=3 Jul=4 Aug=5 Sep=6 Oct=7 Nov=8 Dec=9 Jan=10 Feb=11 Mar=12

so not changing Apr to 4 and May to 5 etc

so be like 201501 ,201502,201503 etc for each set of month sections in the blank space?

Kind regards

Ombir · Aug 18, 2016

Try this:

Code:

Sub Seth()
Dim i       As Long
Dim cell    As Range
Dim rng     As Range
Dim ar      As Range
Dim month   As Variant
Dim arr     As Variant
Dim Sh      As Worksheet

month = Array("Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar")

Set Sh = Worksheets("Sheet2")

For Each ar In Sheets("Sheet1").Range("A1:ZZ1").SpecialCells(xlCellTypeConstants).Areas
    Set rng = ar.CurrentRegion
    rng.Copy Sh.Range("A1").Offset(i)
    i = i + rng.Rows.Count
Next

i = 1
Set rng = Sh.UsedRange
ReDim arr(rng.Rows.Count - 1)
arr(0) = "Period"

On Error Resume Next
For Each cell In Application.Index(rng, 0, 3).Offset(1)
    arr(i) = cell.Offset(, 1) & Format(Application.Match(cell, month, 0), "00")
    i = i + 1
Next
On Error GoTo 0

Sh.Range("G1").Resize(i - 1) = Application.Transpose(arr)

Sh.Range("A1").AutoFilter Field:=1, Criteria1:="OP"
Application.DisplayAlerts = False
Sh.AutoFilter.Range.Offset(1).Delete xlShiftUp
Application.DisplayAlerts = True
Sh.AutoFilterMode = False
End Sub

seth333 · Aug 18, 2016

Ombir said:

Try this:

Code:

Sub Seth()
Dim i       As Long
Dim cell    As Range
Dim rng     As Range
Dim ar      As Range
Dim month   As Variant
Dim arr     As Variant
Dim Sh      As Worksheet

month = Array("Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar")

Set Sh = Worksheets("Sheet2")

For Each ar In Sheets("Sheet1").Range("A1:ZZ1").SpecialCells(xlCellTypeConstants).Areas
    Set rng = ar.CurrentRegion
    rng.Copy Sh.Range("A1").Offset(i)
    i = i + rng.Rows.Count
Next

i = 1
Set rng = Sh.UsedRange
ReDim arr(rng.Rows.Count - 1)
arr(0) = "Period"

On Error Resume Next
For Each cell In Application.Index(rng, 0, 3).Offset(1)
    arr(i) = cell.Offset(, 1) & Format(Application.Match(cell, month, 0), "00")
    i = i + 1
Next
On Error GoTo 0

Sh.Range("G1").Resize(i - 1) = Application.Transpose(arr)

Sh.Range("A1").AutoFilter Field:=1, Criteria1:="OP"
Application.DisplayAlerts = False
Sh.AutoFilter.Range.Offset(1).Delete xlShiftUp
Application.DisplayAlerts = True
Sh.AutoFilterMode = False
End Sub

--------

hiya thanks for reply,

It almost worked got to about 1/4 of the second month and then started to populate #N/A ?

May	2015	-2.155	GBP	201502
May	2015	-0.1975	GBP	201502
May	2015	-2220	GBP	201502
May	2015	-68.408333	GBP	#N/A
May	2015	0	GBP	#N/A
May	2015	235.68546	GBP	#N/A
May	2015	-982.93932	GBP	#N/A
May	2015	-219.22333	GBP	#N/A

and continues all the way down? any ideas?

thanks again

Ombir · Aug 18, 2016

Its working for me without any errors. May be you're running with wrong structure. Can't say anything without testing on data you're trying.

seth333 · Aug 22, 2016

Hiya sorry for late reply,

I have taken a sample of the workbook and tested again , it appears that it keeps in the headings from the columns its transposing , is there a way to not ?

http://jmp.sh/v/nPRPck08uS07e005XqtI

thanks

Ombir · Aug 22, 2016

seth333 said:
Hiya sorry for late reply,

I have taken a sample of the workbook and tested again , it appears that it keeps in the headings from the columns its transposing , is there a way to not ?

http://jmp.sh/v/nPRPck08uS07e005XqtI

thanks

Initially you posted that Name of first column is OP but now its opc.

Change this in above code:

Code:

Criteria1:="OP"

to

Code:

Criteria1:="opc"

seth333 · Aug 22, 2016

Ombir said:
Initially you posted that Name of first column is OP but now its opc.

Change this in above code:

Code:

Criteria1:="OP"

to

Code:

Criteria1:="opc"

hiya , thanks for reply ,

I have change the code to reflect correct column name, but its still getting the #N/A in the Period Column

May	2015	-2.155	GBP	201502
May	2015	-0.1975	GBP	201502
May	2015	-2220	GBP	201502
May	2015	-68.408333	GBP	#N/A
May	2015	0	GBP	#N/A
May	2015	235.68546	GBP	#N/A
May	2015	-982.93932	GBP	#N/A
May	2015	-219.22333	GBP	#N/A

Ombir · Aug 22, 2016

I tried on your sample workbook and its working fine for me.

ExpireBox | Test Workbook.xlsm

Transpose set number of columns to rows after next blank column

seth333

New Member

Excel Facts

Ombir

Active Member

seth333

New Member

Ombir

Active Member

seth333

New Member

Ombir

Active Member

seth333

New Member

Ombir

Active Member

seth333

New Member

Ombir

Active Member

Similar threads

Forum statistics

Share this page

Transpose set number of columns to rows after next blank column

New Member

Excel Facts

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock