Dynamically merging multiple columns of data into one column, organised by a specific criteria...

Z_Sutcliffe123

New Member
Joined
Apr 5, 2019
Messages
8
Hello,

I am working with a spreadsheet that is split by Parts
35f42142ce6838c34aaf9bb4025e5a52
as you can see below.

35f42142ce6838c34aaf9bb4025e5a52
35f42142ce6838c34aaf9bb4025e5a52.png



I need column A to pull revenue from columns F, H and I organised hierarchically by the date. So for example, cell A2 should contain "14/02/2019" because that is the earliest date and then B2 should contain the revenue figure adjacent to it "£100". Then A3 should pull "26/02/2019" and then B3 should contain that date's adjacent revenue figure "£324.11". This also needs to be dynamic. So if I put in a date "10/02/19", then I that should be placed in cell A2 with it's corresponding revenue in B2. The figures that were originally placed in A2, A3, B2 and then B3 get shifted downwards.

I appreciate any help or guidance! :)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
are you able to use PowerQuery aka Get&Transform ?

LDateDateRevenueDateRevenuePart1Revenue1Part2Revenue2Part3Revenue3
17/05/2019​
17/05/2019​
200​
14/02/2019​
100​
26/02/2019
324.11​
25/02/2019
1700​
17/05/2019
200​
25/02/2019​
1700​
06/03/2019
311.22​
25/04/2019
100​
18/05/2019
150​
26/02/2019​
324.11​
29/03/2019
40.22​
14/02/2019
100​
10/03/2019
400​
06/03/2019​
311.22​
10/03/2019​
400​
29/03/2019​
40.22​
25/04/2019​
100​
17/05/2019​
200​
18/05/2019​
150​
 
Last edited:
Upvote 0
Maybe,

Copy the formulas in column A and B down far enough to pull all the dates you think you will have. Adjust ranges so that get all the data you think you will have in columns E to J. This is assuming you will not have any duplicate dates.


Book1
ABCDEFGHIJ
1Dateoverall RevenuePart 1Revenue (Part 1)Part 2Revenue (Part 2)Part 3Revenue (Part 3)
22/14/20191002/26/2019324.112/25/201917005/17/2019200
32/25/201917003/6/2019311.224/25/20191005/18/2019150
42/26/2019324.113/29/201940.222/14/20191003/10/2019400
53/6/2019311.22
63/10/2019400
73/29/201940.22
84/25/2019100
95/17/2019200
105/18/2019150
11  
12  
13  
14  
Sheet3
Cell Formulas
RangeFormula
A2{=IF(ROWS(A$2:A2)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A2)))}
A3{=IF(ROWS(A$2:A3)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A3)))}
A4{=IF(ROWS(A$2:A4)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A4)))}
A5{=IF(ROWS(A$2:A5)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A5)))}
A6{=IF(ROWS(A$2:A6)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A6)))}
A7{=IF(ROWS(A$2:A7)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A7)))}
A8{=IF(ROWS(A$2:A8)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A8)))}
A9{=IF(ROWS(A$2:A9)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A9)))}
A10{=IF(ROWS(A$2:A10)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A10)))}
A11{=IF(ROWS(A$2:A11)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A11)))}
A12{=IF(ROWS(A$2:A12)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A12)))}
A13{=IF(ROWS(A$2:A13)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A13)))}
A14{=IF(ROWS(A$2:A14)>COUNT($E$2:$E$10)+COUNT($G$2:$G$10)+COUNT($I$2:$I$10),"",SMALL(IF(LEFT($E$1:$J$1,1)="p",IF($E$2:$J$10<>0,$E$2:$J$10)),ROWS(A$2:A14)))}
B2{=IF(A2="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A2,$E$2:$E$10,0),"")&IFERROR(MATCH(A2,$G$2:$G$10,0),"")&IFERROR(MATCH(A2,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A2,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A2,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A2,$I$2:$I$10,0))),6)))))}
B3{=IF(A3="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A3,$E$2:$E$10,0),"")&IFERROR(MATCH(A3,$G$2:$G$10,0),"")&IFERROR(MATCH(A3,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A3,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A3,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A3,$I$2:$I$10,0))),6)))))}
B4{=IF(A4="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A4,$E$2:$E$10,0),"")&IFERROR(MATCH(A4,$G$2:$G$10,0),"")&IFERROR(MATCH(A4,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A4,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A4,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A4,$I$2:$I$10,0))),6)))))}
B5{=IF(A5="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A5,$E$2:$E$10,0),"")&IFERROR(MATCH(A5,$G$2:$G$10,0),"")&IFERROR(MATCH(A5,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A5,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A5,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A5,$I$2:$I$10,0))),6)))))}
B6{=IF(A6="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A6,$E$2:$E$10,0),"")&IFERROR(MATCH(A6,$G$2:$G$10,0),"")&IFERROR(MATCH(A6,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A6,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A6,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A6,$I$2:$I$10,0))),6)))))}
B7{=IF(A7="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A7,$E$2:$E$10,0),"")&IFERROR(MATCH(A7,$G$2:$G$10,0),"")&IFERROR(MATCH(A7,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A7,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A7,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A7,$I$2:$I$10,0))),6)))))}
B8{=IF(A8="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A8,$E$2:$E$10,0),"")&IFERROR(MATCH(A8,$G$2:$G$10,0),"")&IFERROR(MATCH(A8,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A8,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A8,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A8,$I$2:$I$10,0))),6)))))}
B9{=IF(A9="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A9,$E$2:$E$10,0),"")&IFERROR(MATCH(A9,$G$2:$G$10,0),"")&IFERROR(MATCH(A9,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A9,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A9,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A9,$I$2:$I$10,0))),6)))))}
B10{=IF(A10="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A10,$E$2:$E$10,0),"")&IFERROR(MATCH(A10,$G$2:$G$10,0),"")&IFERROR(MATCH(A10,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A10,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A10,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A10,$I$2:$I$10,0))),6)))))}
B11{=IF(A11="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A11,$E$2:$E$10,0),"")&IFERROR(MATCH(A11,$G$2:$G$10,0),"")&IFERROR(MATCH(A11,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A11,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A11,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A11,$I$2:$I$10,0))),6)))))}
B12{=IF(A12="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A12,$E$2:$E$10,0),"")&IFERROR(MATCH(A12,$G$2:$G$10,0),"")&IFERROR(MATCH(A12,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A12,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A12,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A12,$I$2:$I$10,0))),6)))))}
B13{=IF(A13="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A13,$E$2:$E$10,0),"")&IFERROR(MATCH(A13,$G$2:$G$10,0),"")&IFERROR(MATCH(A13,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A13,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A13,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A13,$I$2:$I$10,0))),6)))))}
B14{=IF(A14="","",INDEX($E$2:$J$10,(IFERROR(MATCH(A14,$E$2:$E$10,0),"")&IFERROR(MATCH(A14,$G$2:$G$10,0),"")&IFERROR(MATCH(A14,$I$2:$I$10,0),""))+0,IF(NOT(ISERROR(MATCH(A14,$E$2:$E$10,0))),2,IF(NOT(ISERROR(MATCH(A14,$G$2:$G$10,0))),4,IF(NOT(ISERROR(MATCH(A14,$I$2:$I$10,0))),6)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank you that has appeared to have helped. However, I need to apply this to a much larger data set:
147883fa885080ab4a3d440689e9c8d6.png


I effectively need the "Audience", Audience Volume, "Estimated Rev" and "EPCM" values from each part to get pulled in to the overall box, organised by date. The screen shot I had took is quite large and so the parts go all the way to part 7. Part 7's "EPCM" column is AE. So right now the first date (Cell B35) in the "Overall" would be the "04/05/2009" in part 6 because that is the earliest date. The cells adjacent to Cell B35 would contain the data in the columns "Audience Type", "Audience Volume", "Estimated Revenue" and "EPCM" that this date is referring too.

So for example cells B35 to F35 would like like:

"04/01/2019" ---- "Male" ---- "100,000" ---- "£700" ---- "11"

And then next row will contain the second earliest date pulling that date's data for "Audience Type", "Audience Volume", "Estimated Revenue" and "EPCM".

I understand that this may be a super complex and long formula but any help would be greatly appreciated.
 
Upvote 0
Is there no Part 4? the screen shot show part 3 then Part 5. Is this correct?

Will the ranges change?
 
Last edited:
Upvote 0
Try

Right click on the sheet name and select View Code
past the code below into the VBA window.

If your ranges change, for example if a part extends past line 28 , then the code would need to be updated with the new ranges.

This code will run anytime a cell in the range B3:AJ28 is changed.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B3:AJ28")) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
    Dim lr As Long
    
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    If lr > 34 Then Range("B35:F" & lr).ClearContents
    
    If Cells(29, "B").End(xlUp).Row > 2 Then
        Range("B3:F" & Cells(29, "B").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "G").End(xlUp).Row > 2 Then
        Range("G3:K" & Cells(29, "G").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "L").End(xlUp).Row > 2 Then
        Range("L3:P" & Cells(29, "L").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "Q").End(xlUp).Row > 2 Then
        Range("Q3:U" & Cells(29, "Q").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "V").End(xlUp).Row > 2 Then
        Range("V3:Z" & Cells(29, "V").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "AA").End(xlUp).Row > 2 Then
        Range("AA3:Ae" & Cells(29, "AA").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    If Cells(29, "AF").End(xlUp).Row > 2 Then
        Range("AF3:AJ" & Cells(29, "AF").End(xlUp).Row).Copy Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
    End If
    
    lr = Cells(Rows.Count, "B").End(xlUp).Row
        
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range("B35"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("B35:F" & lr)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Target.Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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