SOLVED

Macro to find missing data points from list

Iron Contributor

Hi All,

 

I have a spreadsheet which records prices for items each per day (10am to 5pm). The spreadsheet records prices for many funds and timeslots, so when reviewing to see if any were missed for a period can be time consuming and hard to identify. 

 

To help with this I was wondering if I can use a macro to find missing prices, given we have an expected price for 10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00 each day it has any price. So if a fund has no data/row of information at all for say 17:00, but prices for all other times on the same date it has been missed.

 

I have put together an example with two funds, being ABC and CAT. The example has prices for both funds at each time point on the first date, however then the 17:00 data is missing for CAT on the second. I would manually find this and put the information on the missing prices tab.

 

Since the spreadsheet can cover years of data, i often review a month or quarter at a time. So I am hoping to only include missed prices which are between the start and end of period dates given on the analysis tab.

 

Greatly appreciate any tips or insight on how to do this.

 

Many thanks

3 Replies
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

See the attached workbook.

Hi Hans,

This is brillant, thank you so much! You're an asset to the excel community.

Kind regards,

Callum

@calof1 

You're welcome! Glad to have been able to help.

1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

See the attached workbook.

View solution in original post