table data

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello

I have three columns of data which I would like to get into a table.

I cant use a pivot table as each column is alpha none are numeric What I am
after is some kind of look up function that would look up both the column
heading in column 1 and row title in column 2 and then return the data in
column 3

Something like below....

Has any body got any ideas?

Thanks

Raw......

Column Row Data

Budget1 Code1 unique1

Budget1 Code2 unique2

Budget1 Code3 unique3

Budget2 Code1 unique4

Budget2 Code2 unique5

Budget2 Code3 unique6

Budget3 Code1 unique7

Budget3 Code2 unique8

Budget3 Code3 unique9


Table......

Budget1 Budget2 Budget3

Code1 unique1 unique4 unique7

Code2 unique2 unique5 unique8

Code3 unique3 unique6 unique9
 
Assumptions:

A2:C10 contains the source/raw data

E2:E4 contains Code1, Code2, and Code 3

F1:H1 contains Budget1, Budget2, and Budget3

Formula:

F2, copied down and acorss:

=INDEX($C$2:$C$10,MATCH(1,IF($A$2:$A$10=F$1,IF($B$2:$B$10=$E2,1)),0))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!
 
You can use Pivot Table, but it requires translating the results.
Create another column (Data3) with unique numbers,
maybe with =--RIGHT(Data2,1), arranged like this:

Column Row Data3 Data2
Budget1 Code1 1 unique1
Budget1 Code2 2 unique2
Budget1 Code3 3 unique3
Budget2 Code1 4 unique4
Budget2 Code2 5 unique5
Budget2 Code3 6 unique6
Budget3 Code1 7 unique7
Budget3 Code2 8 unique8
Budget3 Code3 9 unique9

Create a Pivot Table with the first 3 columns, like this:

Row Budget1 Budget2 Budget3
Code1 1 4 7
Code2 2 5 8
Code3 3 6 9

Name Data3, Data2 with the suggested names.
Name the Pivot Table data field ArrayPT.
Create a table, the same size as the data field (3x3),
with this array formula (use Ctrl+Shift+Enter):
=VLOOKUP(ArrayPT,Data3:Data2,2)
Copy column/row headers so the table looks like this:

Budget1 Budget2 Budget3
Code1 unique1 unique4 unique7
Code2 unique2 unique5 unique8
Code3 unique3 unique6 unique9
 
Back
Top