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

Z_Sutcliffe123 · May 22, 2019

Hello,

I am working with a spreadsheet that is split by Parts

as you can see below.

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!

sandy666 · May 22, 2019

are you able to use PowerQuery aka Get&Transform ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]LDate[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Revenue[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Revenue[/td][td][/td][td=bgcolor:#5B9BD5]Part1[/td][td=bgcolor:#5B9BD5]Revenue1[/td][td=bgcolor:#5B9BD5]Part2[/td][td=bgcolor:#5B9BD5]Revenue2[/td][td=bgcolor:#5B9BD5]Part3[/td][td=bgcolor:#5B9BD5]Revenue3[/td][/tr]

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

17/05/2019

[/td][td][/td][td=bgcolor:#E2EFDA]

17/05/2019

[/td][td=bgcolor:#E2EFDA]

200

[/td][td][/td][td=bgcolor:#E2EFDA]

14/02/2019

[/td][td=bgcolor:#E2EFDA]

100

[/td][td][/td][td=bgcolor:#DDEBF7]26/02/2019[/td][td=bgcolor:#DDEBF7]

324.11

[/td][td=bgcolor:#DDEBF7]25/02/2019[/td][td=bgcolor:#DDEBF7]

1700

[/td][td=bgcolor:#DDEBF7]17/05/2019[/td][td=bgcolor:#DDEBF7]

200

[/td][/tr]

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

25/02/2019

[/td][td]

1700

[/td][td][/td][td]06/03/2019[/td][td]

311.22

[/td][td]25/04/2019[/td][td]

100

[/td][td]18/05/2019[/td][td]

150

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

26/02/2019

[/td][td=bgcolor:#E2EFDA]

324.11

[/td][td][/td][td=bgcolor:#DDEBF7]29/03/2019[/td][td=bgcolor:#DDEBF7]

40.22

[/td][td=bgcolor:#DDEBF7]14/02/2019[/td][td=bgcolor:#DDEBF7]

100

[/td][td=bgcolor:#DDEBF7]10/03/2019[/td][td=bgcolor:#DDEBF7]

400

[/td][/tr]

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

06/03/2019

[/td][td]

311.22

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

10/03/2019

[/td][td=bgcolor:#E2EFDA]

400

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

29/03/2019

[/td][td]

40.22

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

25/04/2019

[/td][td=bgcolor:#E2EFDA]

100

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

17/05/2019

[/td][td]

200

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

18/05/2019

[/td][td=bgcolor:#E2EFDA]

150

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Z_Sutcliffe123 · May 22, 2019

I don't think so, no.

sandy666 · May 22, 2019

no problem

have a nice day

Scott T · May 22, 2019

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

A

B

C

D

E

F

G

H

I

J

1

Date

overall Revenue

Part 1

Revenue (Part 1)

Part 2

Revenue (Part 2)

Part 3

Revenue (Part 3)

2

2/14/2019

100

2/26/2019

324.11

2/25/2019

1700

5/17/2019

200

3

2/25/2019

1700

3/6/2019

311.22

4/25/2019

100

5/18/2019

150

4

2/26/2019

324.11

3/29/2019

40.22

2/14/2019

100

3/10/2019

400

5

6

7

8

9

10

11

12

13

14

Sheet3

Cell Formulas
Range	Formula
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.

Z_Sutcliffe123 · May 22, 2019

Thank you that has appeared to have helped. However, I need to apply this to a much larger data set:

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.

Scott T · May 22, 2019

Is there no Part 4? the screen shot show part 3 then Part 5. Is this correct?

Will the ranges change?

Z_Sutcliffe123 · May 23, 2019

Oh that was my mistake. Here is the amended screenshot:

Scott T · May 23, 2019

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

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

Z_Sutcliffe123

New Member

Excel Facts

sandy666

Banned - Rules violations

Z_Sutcliffe123

New Member

sandy666

Banned - Rules violations

Scott T

Well-known Member

Z_Sutcliffe123

New Member

Scott T

Well-known Member

Z_Sutcliffe123

New Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Banned - Rules violations

New Member

Banned - Rules violations

Well-known Member

New Member

Well-known Member

New Member

Well-known 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