SOLVED

Need help with finding data from two differing columns

Copper Contributor

Good Morning,

 

I have a table with a Shop Order, A date, A name and list of hours.  I am then using this info in different ways.  I am currently trying to track the dates that a person has worked on a shop order.  I had initially though of having a table with names on top and the days of the month on the left and then just have a Yes or a No if each person did work on that day.  However, in reading thru allot of help I could potentially use XLOOKUP or MATCH, but I am having problems 1. getting it to work right and 2. understanding what these do.  I read that I could use an ARRAY, but that is way over my head and I don't know how to format it right.  I would be ok with just a list of names with the associated dates.  What ever would be an easier path.  I just want a quick way to look up if each person has a record for each day.  Hope this is understandable.  Thank you for any help you may be able to provide.  Here is a sample table of the data.

Sales OrderDateName Hours
1234510/4/2023Jack4.5
2468010/4/2023Jack

4.5

1234510/4/2023Joe9
1002310/5/2023John9
5641210/6/2023Jill9
    
    
    
4 Replies

@Paul_Farquhar 

See the attached demo. It uses just 3 formulas.

best response confirmed by HansVogelaar (MVP)
Solution

@Paul_Farquhar 

 

An alternative would be to insert a Pivot Table, as demonstrated in the attached file and change the NumberFormat of one of the Values cells to "Yes";; to achieve the desired output.

 

 

@Subodh_Tiwari_sktneer thank you all for the suggestions.  I ended up with the pivot table.  While the formulas worked great on my home computer when I tried using them at work, I had issues with it.  I think that is because at work we use Excel 2016.

 

Thank you again,

Paul

You're welcome @Paul_Farquhar! Glad it worked for you.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@Paul_Farquhar 

 

An alternative would be to insert a Pivot Table, as demonstrated in the attached file and change the NumberFormat of one of the Values cells to "Yes";; to achieve the desired output.

 

 

View solution in original post