Creating a formula or vba for collating data and monthly reports

[sikhwizard]

So basically I have a Month's data to collate. The data is sent daily on a workbook (Lets say that the spreadsheet follows the naming convention 2014-03-01, </SPAN>2014-03-02, 2014-03-03 etc)

In the workbook, it contains a sheet that has columns and rows that are related.

To help visualise;
The columns are Letters
And the rows are Numbers


On the 2014-03-01, I could for example have the following Letters;

A, B, C, D, E, F, G


And the following for Numbers

1, 2, 3, 4, 5, 6 and 7.


There are values in the cells where the column and row meet;

for example.

Column has Letter A and where there is a row Number 1, this has a value in the cell displayed as 8</SPAN>

[TABLE="width: 171"]
<TBODY>[TR]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"][/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]A
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]B
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]C
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]D
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]E
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]F
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]G
[/TD]
[TD="class: xl71, width: 25, bgcolor: #c5d9f1"]H
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]1
[/TD]
[TD="class: xl72, width: 25, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]A
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]B
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]C
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]D
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]E
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]F
[/TD]
[TD="class: xl73, width: 25, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]2
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]3
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]4
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]7
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]5
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]6
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]7
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #c5d9f1"]8
[/TD]
[TD="class: xl74, width: 25, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, width: 25, bgcolor: transparent"]3
[/TD]
[/TR]
</TBODY>[/TABLE]


However,

the next days data 2014-03-02 might not contain all the same values in the columns/rows as the day before.</SPAN>

[TABLE="width: 171"]
<TBODY>[TR]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"][/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]A
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]B
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]C
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]D
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]E
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]F
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]G
[/TD]
[TD="class: xl73, width: 25, bgcolor: #c5d9f1"]H
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]A
[/TD]
[TD="class: xl71, bgcolor: transparent"]B
[/TD]
[TD="class: xl71, bgcolor: transparent"]E
[/TD]
[TD="class: xl71, bgcolor: transparent"]D
[/TD]
[TD="class: xl71, bgcolor: transparent"]T
[/TD]
[TD="class: xl71, bgcolor: transparent"]R
[/TD]
[TD="class: xl71, bgcolor: transparent"]X
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]2
[/TD]
[TD="class: xl71, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]2
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]3
[/TD]
[TD="class: xl71, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]4
[/TD]
[TD="class: xl71, bgcolor: transparent"]7
[/TD]
[TD="class: xl70, bgcolor: transparent"]7
[/TD]
[TD="class: xl70, bgcolor: transparent"]7
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]2
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]7
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]5
[/TD]
[TD="class: xl71, bgcolor: transparent"]8
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]6
[/TD]
[TD="class: xl71, bgcolor: transparent"]11
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]2
[/TD]
[TD="class: xl70, bgcolor: transparent"]8
[/TD]
[TD="class: xl70, bgcolor: transparent"]7
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]7
[/TD]
[TD="class: xl71, bgcolor: transparent"]16
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]6
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #c5d9f1"]8
[/TD]
[TD="class: xl71, bgcolor: transparent"]67
[/TD]
[TD="class: xl70, bgcolor: transparent"]7
[/TD]
[TD="class: xl70, bgcolor: transparent"]2
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]4
[/TD]
[TD="class: xl70, bgcolor: transparent"]3
[/TD]
[/TR]
</TBODY>[/TABLE]


So my thought process was to be able to write some kind of query where it says that in axis X (Column) where there is a value for Letter that = A & in axis Y (Row) where there is a value for that Number = 1, find the CELL and place the value in that cell into another sheet – Hope that make sense?


I have played around a little with formulas and even thought about doing this in a Macros, but not sure what the best way is.

Is the IF statement or the INDEX the best way to get around this problem.
I am trying to look at the best way to collate the data daily and be able to create a Monthly report on the information once it has been collated.
So I am thinking of creating a template that will cover all the column (A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z) and row (1,2,3,4,5,6,7,8,9,0) variables, drop the daily report in there, run a query/Macro to do the anaylsis and then throw out the relevant data into a new sheet and then create a report - any idea's?

Thanks

 

[sikhwizard]

Anbody?