VBA - Find Missing Value and List in separate sheet

[tommiexboi]

Hello Everyone!

Purpose
I'm creating a forecast tool that lists both quantity and price. I need the users to make sure that if they enter a quantity then they need to input a price.

Objective
I want to create a macro where it'll loop through the data and check if there's a quantity there's also a price. If there's no price, then it'll list the Customer, Item Number, and which month it's missing from on a separate sheet.

Example Data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Item[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD][/TD]
[TD]Month 1 Price[/TD]
[TD]Month 2 Price[/TD]
[TD]Month 3 Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]ABC-123[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]$5.00[/TD]
[TD]$3.00[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]XYZ-456[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD]$1.00[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Result in separate sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Item[/TD]
[TD]Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]ABC-123[/TD]
[TD]Month 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]XYZ-456[/TD]
[TD]Month 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]XYZ-456[/TD]
[TD]Month 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Need Help:
I have tried to look through the vast amount of information on the internet, however; this specific case seems hard to find. Is there a way I can create a macro to do this or a formula that can replicate this?

Any help or direction will be greatly appreciated!
Thank you

 

[Rick Rothstein]

Do you really only have three months worth of data or did you simplify your question for us? If the latter, please show us the complete header list (we don't need to know the prices, just the actual data layout).

 

[tommiexboi]

Do you really only have three months worth of data or did you simplify your question for us? If the latter, please show us the complete header list (we don't need to know the prices, just the actual data layout).

Hi Rick,

I simplified it as the data is quite large, but the complete header looks like below.

The months with "actual's" are historical data that I'm pulling from a different table, but the months without the "actuals" are the cells that the user needs to enter.

Hope this helps
[TABLE="width: 2428"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 14"]Quantity[/TD]
[TD="colspan: 14"]ASP[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item Number[/TD]
[TD]Product Type[/TD]
[TD]End Customer[/TD]
[TD]Notes[/TD]
[TD][/TD]
[TD]Jun'17 Actuals[/TD]
[TD]Jul'17 Actuals[/TD]
[TD]Aug'17 Actuals[/TD]
[TD]Sep'17 Actuals[/TD]
[TD]Sep'17[/TD]
[TD]Oct'17[/TD]
[TD]Nov'17[/TD]
[TD]Dec'17[/TD]
[TD]Jan'18[/TD]
[TD]Feb'18[/TD]
[TD]Mar'18[/TD]
[TD]Apr'18[/TD]
[TD]May'18[/TD]
[TD]Jun'18[/TD]
[TD]Jun'17 Actuals[/TD]
[TD]Jul'17 Actuals[/TD]
[TD]Aug'17 Actuals[/TD]
[TD]Sep'17 Actuals[/TD]
[TD]Sep'17[/TD]
[TD]Oct'17[/TD]
[TD]Nov'17[/TD]
[TD]Dec'17[/TD]
[TD]Jan'18[/TD]
[TD]Feb'18[/TD]
[TD]Mar'18[/TD]
[TD]Apr'18[/TD]
[TD]May'18[/TD]
[TD]Jun'18[/TD]
[/TR]
</tbody>[/TABLE]