I am looking to take a small amount of data (onto a separate sheet) by extracting it from a large table.
I am working of of formulas (by altering them) from a different, but very similar project which worked very well.
For simplicity I will tone down the set of data.
Sheet1 consists of the following:
AA1 BB2 CC3
TT45 45 15 84
HH41 21 100 185
FF87 47 5 204
TT89 12 78 890
GG84 2 14 780
Basically a simple matrix. No formulas needed here.
Sheet 2
I am looking to take data from sheet1 for a more straight-forward view
2 3
BB2 CC3
4 TT89 #VALUE! #VALUE!
1 TT45 #VALUE! #VALUE!
The first row gives which columns the text string falls into:
e.g. BB2 is the 2nd column in that set (this is later used in formulation):
The 2 is found from the following:
3 the same way.
The first column works the same by given which row it falls into (on sheet1)
Found:
The problem is I cant seem to get the index (i.e. BB2 is in column2, TT89 is in row4, therefore the number it should be grabbing is '78' from sheet1
This also uses a module (and assumes headers):
Function Com(rng As Range) As String
Dim Rw As Range
Dim txt As String
For Each Rw In rng
txt = txt & Rw & ", "
Next Rw
Com = Left(txt, Len(txt) - 2)
End Function
I might be making this more difficult than it is, and there could be a quicker way to complete it
I am working of of formulas (by altering them) from a different, but very similar project which worked very well.
For simplicity I will tone down the set of data.
Sheet1 consists of the following:
AA1 BB2 CC3
TT45 45 15 84
HH41 21 100 185
FF87 47 5 204
TT89 12 78 890
GG84 2 14 780
Basically a simple matrix. No formulas needed here.
Sheet 2
I am looking to take data from sheet1 for a more straight-forward view
2 3
BB2 CC3
4 TT89 #VALUE! #VALUE!
1 TT45 #VALUE! #VALUE!
The first row gives which columns the text string falls into:
e.g. BB2 is the 2nd column in that set (this is later used in formulation):
The 2 is found from the following:
Code:
=IF(C3="","",MATCH(C3,Sheet1!$B$2:$D$2,0))The first column works the same by given which row it falls into (on sheet1)
Found:
Code:
=IF(B5="","",MATCH(B5,Sheet1!$A$3:$A$6,0))The problem is I cant seem to get the index (i.e. BB2 is in column2, TT89 is in row4, therefore the number it should be grabbing is '78' from sheet1
This also uses a module (and assumes headers):
Function Com(rng As Range) As String
Dim Rw As Range
Dim txt As String
For Each Rw In rng
txt = txt & Rw & ", "
Next Rw
Com = Left(txt, Len(txt) - 2)
End Function
I might be making this more difficult than it is, and there could be a quicker way to complete it
