List to Table - formula
 

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 cant 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

--
Thanks

Joe

List to Table - formula
 

The tavle lost the format in the previous post

hope this now makes sence

thanks for your help

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
--
Thanks

Joe


"Joe" wrote:

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 cant 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

--
Thanks

Joe

List to Table - formula
 

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 B4:D12 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

"Joe" wrote in message
...
The tavle lost the format in the previous post

hope this now makes sence

thanks for your help

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
--
Thanks

Joe


"Joe" wrote:

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 cant 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

--
Thanks

Joe

List to Table - formula
 

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