• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.

73948
 

Attachments

  • Sample.xlsx
    9.6 KB · Views: 6
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?
 
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

  • Copy of Sample_Chandoo46026.xlsx
    10.7 KB · Views: 4
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

  • Chandoo46026Sample.xlsx
    19.6 KB · Views: 3
Back
Top