# Data Series

#### andrewchen0019

##### New Member
Dear Excel community,

I am dealing with a large database and I am trying to make the key the same for all those line with the same event name. I am trying to find a formula and not copy and paste method. Greatly appreciate if anyone could give me any advice.

#### Attachments

• 9.6 KB Views: 6

#### AliGW

##### Active Member
Don't get it - where is the data coming from?

#### vletm

##### Excel Ninja
andrewchen0019
Is there some kind clear logic, how those keys should come?

#### andrewchen0019

##### New Member
Don't get it - where is the data coming from?

Basically the key represents the dates. For example, ACES1024E represents 24 October.

Ultimately, I am trying to find the event start date and event end date from the keys by adding another 2 columns (which is not here). For this instance, because all the event name is "ABC", therefore, all should have the a start date of 24 October and end date of 28 October from the key of "ACES1024E,ACES1025E,ACES1026D,ACES1027D,ACES1027E,ACES1028D,ACES1028E".

However, I am facing the problem of coming up with a formula or algorithm in making all the events with the same names to have the full lists of keys so that I could obtain the start date and end date. Any idea on how I could workaround this problem?

#### GraH - Guido

##### Well-Known Member
going by your workbook and if you have a modern enough Excel, I'm guessing the following formula does the trick.
[C2]=XLOOKUP(A2,Key!A:A,Key!B:B,NA(),0,-1)
for older, not too old versions, maybe this
[C2]=INDEX(Key!B:B,AGGREGATE(14,6,ROW(Key!A:A)/(Key!A:A=Expected!A2),1))

Did use lazy references, adapt to your situation as it is not really advised to reference full columns. Just shows the technique.

#### Attachments

• 10.7 KB Views: 4

#### p45cal

##### Well-Known Member
the problem of coming up with a formula or algorithm <snip> so that I could obtain the start date and end date.
Would a Power Query solution help?
In the attached change the data in the table on the left and then right-click the table on the right and choose Refresh.
Is there any year data hidden in those keys? Things won't be quite right if an event spans the new year..

#### Attachments

• 19.6 KB Views: 3