How to match quarter data with the correct quarter?

[Waimea]

Hi,

I have a data table with 3 columns, name, value and year and quarter.

Name	Value	Year & quarter
Event A	50	2021 Q1
Event B	45	2021 Q3
Event C	52	2021 Q4
Event D	49	2022 Q2

And I have a table with timevalues:

Timevalue

2021 Q1

2021 Q2

2021 Q3

2021 Q4

2022 Q1

2022 Q2

2022 Q3

How can I in a easy way (with VBA) place the events at the right place in my timetable?

Timevalue	Name	Value
2021 Q1	Event A	50
2021 Q2
2021 Q3	Event B	45
2021 Q4	Event C	52
2022 Q1
2022 Q2	Event D	49

All suggestions are very welcome!

 

[Waimea]

I found that FILTER function kind of does what I am trying to do.

However, I am looking for a VBA solution.

Any suggestion on what functions that I could use?

 

[alansidman]

An alternative is to use Power Query and join the two tables on left outer join

Book2
	A	B	C	D	E	F
1	Name	Value	Year & quarter			Timevalue
2	Event A	50	2021 Q1			2021 Q1
3	Event B	45	2021 Q3			2021 Q2
4	Event C	52	2021 Q4			2021 Q3
5	Event D	49	2022 Q2			2021 Q4
6						2022 Q1
7						2022 Q2
8						2022 Q3
9
10
11	Timevalue	Table1.Name	Table1.Value
12	2021 Q1	Event A	50
13	2021 Q2
14	2021 Q3	Event B	45
15	2021 Q4	Event C	52
16	2022 Q1
17	2022 Q2	Event D	49
18	2022 Q3
Sheet1

let
    Source = Table.NestedJoin(Table2, {"Timevalue"}, Table1, {"Year & quarter"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Name", "Value"}, {"Table1.Name", "Table1.Value"})
in
    #"Expanded Table1"

 

[Waimea]

Hi alansidman,

thank you for your reply!

I am not great with powerquery but I will try your solution tomorrow!

I am trying to use INDEX & MATCH functions to get what I want.

I have an odd number of matches depending on what events I am looking at.

Perhaps there is a good way to use VBA and INDEX & MATCH?

 

[Waimea]

I got it kind of working with index and match formuals in the spreadsheet.

However, I want to try a VBA solution.

=INDEX($AP$387:$AP$396;MATCH(AC384;$AU$387:$AU$396;0))

and

=INDEX($AQ$387:$AQ$396;MATCH(AC384;$AU$387:$AU$396;0))

These are my current formulas and they work, I am trying to create them in VBA.

I would like to assign quarters to events where the year is presented but the quarter is omitted.

So if first data entry is Event A, 50, and 2021 Q2, I want to assign Q3 and Q4.

If the third data entry is 2022 Q1, I want to assign omitted quarters Q2, Q3 and Q4.

 

[kvsrinivasamurthy]

Data A1:C5
Second table A9:C16

Code

Sub TrandferData()
Dim TR As Long
Dim SeachRng As Range

Set SearchRng = Range("C1:C5")

For TR = 10 To 16

Set Frng = SearchRng.Find(What:=Range("A" & TR), After:=Range("C1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

If Not Frng Is Nothing Then
Range("B" & TR) = Range("A" & Frng.Row)
Range("C" & TR) = Range("B" & Frng.Row)
End If

Next TR

End Sub

 

[Waimea]

Hi kvsrinivasamurthy,

thank you for your reply. Your code works really well!

A follow up question, what happens if two events have the same year and quarter?

 

[kvsrinivasamurthy]

Can you post image having such condition

 

[Waimea]

Hi,

thank you for your reply!

Name	Value	Year and quarter
Event A	50	2021 Q1
Event B	46	2021 Q2
Event C	39	2021 Q3
Event D	41	2021 Q3
Event E	48	2022 Q1
Event F	51	2022 Q3
Event G	48	2023 Q1

Event C and Event D both have the same year and quarter, 2021 Q3.

I am not sure on what to do when two events have the same year and quarter, perhaps just add +1 to the last quarter?

So event D would be 2021 Q4!

I am plotting the events on a timeline, perhaps I could change the quarters to contain two units? I tried and it didn't lock any good!

So adding one quarter would be a good way!

 

[Waimea]

Perhaps there is a way to count each occurence of year and quarter and then add +1 quarter if there are two quarters with the same year?