MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- Windows
Hi,
Apologies.. this might be a bit vague, I'm looking for suggestions if possible.
I will have a large matrix - may be 200 x 1000
To simplify the above, imagine I have a matrix of 3 x 3.
Across the top (as headers) are account numbers.
Down the left side are product codes.
On sheet 2, I have 3 columns
Column A - Product Codes
Column B - Account Numbers
Column C - Prices
This is what I would like to do:
Match the product code from Sheet 2, Cell A2 with the product code in Sheet 1, Column A (left of the matrix)
Match the account number from Sheet 2, Cell B2 with the account number in Sheet 1, Row 1 (the headers of the matrix)
Where they intersect in the Sheet 1 matrix, I want it to check if there is already a value in that cell.. if there is I want to highlight that cell in green and then move on to the next check, if there is no value, I would like to insert the corresponding price from Sheet 2, Cell C2
If I was doing it with a formula, I would use Index Match Match but that would overwrite any cells which already had a value in them... which is why I need to put it into code to check the cell first before inserting a value.
I would then want to loop onto the next one which would be match Cell's A3, B3 and insert C3 if there isn't a value already in the intersection cell.
Any suggestions for this please. Please bare in mind that this may end up being a huge matrix but it was easier to describe initially as a 3 x 3 matrix.
Thanks in advance for any suggestions.
Simon
Apologies.. this might be a bit vague, I'm looking for suggestions if possible.
I will have a large matrix - may be 200 x 1000
To simplify the above, imagine I have a matrix of 3 x 3.
Across the top (as headers) are account numbers.
Down the left side are product codes.
On sheet 2, I have 3 columns
Column A - Product Codes
Column B - Account Numbers
Column C - Prices
This is what I would like to do:
Match the product code from Sheet 2, Cell A2 with the product code in Sheet 1, Column A (left of the matrix)
Match the account number from Sheet 2, Cell B2 with the account number in Sheet 1, Row 1 (the headers of the matrix)
Where they intersect in the Sheet 1 matrix, I want it to check if there is already a value in that cell.. if there is I want to highlight that cell in green and then move on to the next check, if there is no value, I would like to insert the corresponding price from Sheet 2, Cell C2
If I was doing it with a formula, I would use Index Match Match but that would overwrite any cells which already had a value in them... which is why I need to put it into code to check the cell first before inserting a value.
I would then want to loop onto the next one which would be match Cell's A3, B3 and insert C3 if there isn't a value already in the intersection cell.
Any suggestions for this please. Please bare in mind that this may end up being a huge matrix but it was easier to describe initially as a 3 x 3 matrix.
Thanks in advance for any suggestions.
Simon