Counting the unique instances of a date in a row, containing different types of data

[gtza]

Hi there

I have a sheet where data is organised in rows. Each column contains different types of data, like dates, free text, numbers etc. Some types of data (e.g., dates) appear in multiple columns and sometimes the same date appears twice.

Is there a formula to count the number of unique dates in each row?

Can I further upgrade this formula to only count unique dates where, for example, the cell always 2 columns before the cell containing the date must contain "yes"?

Thank you!!

[Glenn Kennedy]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[gtza]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ok - please see the attached example. I have made the cells yellow where the formulas should go. The first yellow column just needs to count the unique number of dates in that row, whereas the second column should count that number of unique dates that also have the text "outdoor" in the cell four columns down from each date column.

Thank you.

[Glenn Kennedy]

Have you any idea why the "date" in Col D is not a date, while that in col R, is?? Try changing the format from short date to long date to see what I mean... this is making life a bit difficult.

[gtza]

Have you any idea why the "date" in Col D is not a date, while that in col R, is?? Try changing the format from short date to long date to see what I mean... this is making life a bit difficult.

I see what you mean... it's definitely unintentional, and must be how the data was captured. Is it possible to have an if statement that compensates for these two different formats? In the meantime, I will try get it fixed on my end (it's not something I have direct control over)...

[Glenn Kennedy]

Yes you can compensate, but it'll probably make your Q a lot more tricky to sort out...

[Glenn Kennedy]

half way there... I have the unique dates (after I manually changes a few non-date dates); but I'm struggling with the Outdoor/Indoor part.

[gtza]

half way there... I have the unique dates (after I manually changes a few non-date dates); but I'm struggling with the Outdoor/Indoor part.

Thanks! Sorry about the non-date dates. Couldn't you use some combination of index and match to get the row and column number of the unique value, and then pluck out the Indoors/outdoors value from those co-ordinates? The indoors/outdoors value is always a set no. of columns from the date. Just an idea.

[Glenn Kennedy]

Grrr. I'm not sure if I'm pleased that I have a solution: or ready to kill you 'cos of the anguish getting there caused. I've had to create another sheet, to get the necessary association between the unique dates and the indoor/outdoor bit. You may need to move the dates and the in/out bits further apart (or maybe even on separate sheets??) and extend the range on your really wide sheet.

In the end, OFFSET cracked it. I'll take a look at your other one tomorrow, providing the NYE bubbles haven't got the better of me.

Any problems, shout...

[gtza]

Glenn, that's amazing! It seems to be working perfectly, thank you SO much! I will let you know if there any problems (doubt it though!).