Hello, Good Day!


I have a sample file attached where it as data of which week and reason. Somehow, I would like merge the reasons in unique like = Week 1: "Absent/Late/Undertime" then so on so fort with each week. I tried if formula with the unique and Vlookup function but it's not working.

I would highly appreciate the help.




Thank you.

Attached Files

Sample.xlsx (8.2 KB, 2 views)

Not sure what you really want, but attached is an example how to get what you asked

I used Defined Tables instead ordinary ones.

From your example it remained unclear, how you'd handle week numbers starting from 10th one! In my example, it will be "Week10". In case the week values in your table were markers only, and real entries for them will be something different, you have to make proper corrections to formulas. Btw., I'd use numbers in format "yyyyww" instead (1st week of this year would be 202301). This allows to have data from several years to be entered into your table without problems!

As i did write at start of this post, I can't see any gain you can get from this. In table on Sheet2, i'd have columns where weekly counts for every of reasons would be calculated instead.

Attached Files

WeekInfo.xlsx (13.4 KB, 3 views)

Two offers in the attached:
1. In Sheet1 cell E1, a formula (lambda).
Also derivation of the lambda formula in cell E7 This cell is not needed, only there for information. See Defined names.

2. In sheet Sheet1 PQ, a Power Query solution. Should the data in the source columns A & B change, the result table at cell D1 will need refreshing by right-clicking anywhere in that results table and choosing Refresh.

Attached Files

msOfficeForums51652Sample.xlsx (20.9 KB, 0 views)