List to Table - formula

[Joe]

Hello

I am trying to put a list of data into a table. I would like to use a
formula to do this as the list is updated on a regular basis.

If the ‘data’ was numeric then I could use a pivot table to sum or count but
it is text therefore the pivot table will not work.

I have tried using Vlookup and INDEX but I can’t seem to get the formulas to
work.

below is an example of what i have got and what I wish to create

Can any body help?

Thanks

From…….. To.......
ROW COL DATA DEV ASS COU
AAA DEV RS AAA RS KS PS
AAA ASS KS BBB YS RT KS
AAA COU PS CCC PX IU MN
BBB DEV YS DDD BG TG AC
BBB ASS RT EEE YS EX AC
BBB COU KS
CCC DEV PX
CCC ASS IU
CCC COU MN
DDD DEV BG
DDD ASS TG
DDD COU AC
EEE DEV YS
EEE ASS EX
EEE COU AC

 

[Ashish Mathur]

Hi,

Array enter the following formula (Ctrl+Shift+Enter) in cell G16 and then
copy to the right and downwards

=INDEX($B$4:$D$12,MATCH(1,($B$4:$B$12=$F16)*($C$4:$C$12=G$15),0),3)

I have assumed that the data is in range B412 and in the To.... portion,
the row and column headings are in F16:F18 and G15:I15

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Excel 2007
Table to Pivot Table to text Table.
All dynamic. No formulas. With macro.
http://www.mediafire.com/file/4zemmnjagnz/05_06_09.xlsm