sikhwizard
New Member
- Joined
- Apr 24, 2014
- Messages
- 8
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>
<TBODY>
</TBODY>
However,
the next days data 2014-03-02 might not contain all the same values in the columns/rows as the day before.</SPAN>
<TBODY>
</TBODY>
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
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>
| A | B | C | D | E | F | G | H | |
| 1 | A | B | C | D | E | F | G | |
| 2 | 1 | 8 | 3 | 2 | 5 | 6 | 8 | 1 |
| 3 | 2 | 5 | 4 | 1 | 3 | 8 | 1 | 3 |
| 4 | 3 | 7 | 7 | 1 | 3 | 2 | 6 | 7 |
| 5 | 4 | 5 | 1 | 4 | 1 | 3 | 8 | 5 |
| 6 | 5 | 3 | 6 | 2 | 8 | 7 | 4 | 6 |
| 7 | 6 | 6 | 4 | 8 | 6 | 1 | 5 | 2 |
| 8 | 7 | 7 | 2 | 3 | 5 | 4 | 8 | 3 |
<TBODY>
</TBODY>
However,
the next days data 2014-03-02 might not contain all the same values in the columns/rows as the day before.</SPAN>
| A | B | C | D | E | F | G | H | |
| 1 | A | B | E | D | T | R | X | |
| 2 | 1 | 4 | 3 | 2 | 5 | 6 | 4 | 1 |
| 3 | 3 | 5 | 4 | 1 | 3 | 4 | 1 | 3 |
| 4 | 7 | 7 | 7 | 1 | 3 | 2 | 6 | 7 |
| 5 | 8 | 5 | 1 | 4 | 1 | 3 | 4 | 5 |
| 6 | 11 | 3 | 6 | 2 | 8 | 7 | 4 | 6 |
| 7 | 16 | 6 | 4 | 4 | 6 | 1 | 5 | 2 |
| 8 | 67 | 7 | 2 | 3 | 5 | 4 | 4 | 3 |
<TBODY>
</TBODY>
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
