I have this table in a spreadsheet.

a b c d e f g
1 1 2 3 4 5 6
2 A Z U P K F A
3 B V Q L G B
4 C W R M H C
5 D X S N I D
6 E Y T O J E

Below is what i need have displayed.

a b c d
7 J U M P
8 E5 A2 C4 A3

So i need to use the first grip to make up the answers in second in row 8.

I can use the concatenate to get the Letter and number together, however i
dont know how to get the letters and numbers. I used the lookup however it
only worked for the first row and the rest of the rows dont work? help on
this would be great!!

Hi Kim,

Either I don't understand your question or you made a slight mistake; I would have thought row 8 would have to be

E4 A2 C3 A3


If that is correct, then here is a solution which gives the answer in row 13. You can hide the intermediate rows or change the
formulas and put them somewhere else.

In A8:

=IF(ISNA(MATCH(A$7,$B2:$G2,0)),"",$A2&MATCH(A$7,$B 2:$G2,0))

Copy down to a total of five rows and copy those to the right, to get 6 columns.

In A13, enter:

=A8&A9&A10&A11&A12

Copy to the right to get 6 columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"KimC" wrote in message ...
|I have this table in a spreadsheet.
|
| a b c d e f g
| 1 1 2 3 4 5 6
| 2 A Z U P K F A
| 3 B V Q L G B
| 4 C W R M H C
| 5 D X S N I D
| 6 E Y T O J E
|
| Below is what i need have displayed.
|
| a b c d
| 7 J U M P
| 8 E5 A2 C4 A3
|
| So i need to use the first grip to make up the answers in second in row 8.
|
| I can use the concatenate to get the Letter and number together, however i
| dont know how to get the letters and numbers. I used the lookup however it
| only worked for the first row and the rest of the rows dont work? help on
| this would be great!!

I dont think i explained it right. I am trying to make a code with something
similar to coordinates - so for the letter G the code would be B5, in the
first column i have listed what i am naming the rows and the first row is
what i am naming the columns. the diagram might not make sense due to having
my user listed version and the excel listed as well

"Niek Otten" wrote:

Hi Kim,

Either I don't understand your question or you made a slight mistake; I would have thought row 8 would have to be

E4 A2 C3 A3


If that is correct, then here is a solution which gives the answer in row 13. You can hide the intermediate rows or change the
formulas and put them somewhere else.

In A8:

=IF(ISNA(MATCH(A$7,$B2:$G2,0)),"",$A2&MATCH(A$7,$B 2:$G2,0))

Copy down to a total of five rows and copy those to the right, to get 6 columns.

In A13, enter:

=A8&A9&A10&A11&A12

Copy to the right to get 6 columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"KimC" wrote in message ...
|I have this table in a spreadsheet.
|
| a b c d e f g
| 1 1 2 3 4 5 6
| 2 A Z U P K F A
| 3 B V Q L G B
| 4 C W R M H C
| 5 D X S N I D
| 6 E Y T O J E
|
| Below is what i need have displayed.
|
| a b c d
| 7 J U M P
| 8 E5 A2 C4 A3
|
| So i need to use the first grip to make up the answers in second in row 8.
|
| I can use the concatenate to get the Letter and number together, however i
| dont know how to get the letters and numbers. I used the lookup however it
| only worked for the first row and the rest of the rows dont work? help on
| this would be great!!