How to extract specified data from matrix

[dmb41]

I am looking to take data from a very large matrix.

It is like the following form

--A B C D E .... CC
A 1 5 7 9 1
B 6 7 8 2 3
C 5 7 2 1 6
D 5 3 1 8 3
E 6 3 1 6 8
.
.
.

Say I want to extract the following data: A C E
and be left with something like:
--A C E ....
A 1 7 1
C 5 2 6
E 6 1 8

Is there away just to pull or extract data in these column and rows instead of manually deleted all rows. Reason is I need to do several different combos.




BTW : Is there an easy way to copy and paste the cells into this message (So its easier to visualize). Or does it have to be photo hosted?

 

[Peter_SSs]

You could use the formula in B10, copied across and down. Alternatively, you could save a lot of processing by setting up the results table like my bottom one where
A17 is copied down
C15 is copied across
C17 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G
1		A	B	C	D	E
2	A	1	5	7	9	1
3	B	6	7	8	2	3
4	C	5	7	2	1	6
5	D	5	3	1	8	3
6	E	6	3	1	6	8
7
8
9		A	C	E
10	A	1	7	1
11	C	5	2	6
12	E	6	1	8
13
14
15			1	3	5
16			A	C	E
17	1	A	1	7	1
18	3	C	5	2	6
19	5	E	6	1	8
20
Extract from matrix

Regarding posting screen shots ..
My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[dmb41]

Thank you for the two options! Exactly what I was looking for and needed. This will help very much.

Is the only way to "automate" this process by developing a macro? For example, if I just typed in the three letter A,C,E and it formed the matrix out of it.

The logic that you have provided will definitely work ... I am just wondering if there is a faster way via macro for when there are different sequences I need formed. I'm thinking it might be a nice small project to get myself more familiar with macros, if feasible.

 

[Peter_SSs]

You certainly could set this up with a macro and it sounds like it would be a good exercise for you.

However, a fair job can be done with just formulas if you want. Try this idea.

Your list of letters gets entered in the green cells. They do not need to be in the same order as the original matrix.

A13:A14 copied down as far as you might need.
C12 copied across as far as you might need.
C14 copied across and down as far as the other formulas just mentioned above.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		A	B	C	D	E
2	A	1	5	7	9	1
3	B	6	7	8	2	3
4	C	5	7	2	1	6
5	D	5	3	1	8	3
6	E	6	3	1	6	8
7
8
9
10
11	4
12			1	3	5	2
13			A	C	E	B
14	1	A	1	7	1	5
15	3	C	5	2	6	7
16	5	E	6	1	8	3
17	2	B	6	8	3	7
18
19
20
21
22
23
Extract from matrix

 

[dmb41]

Thanks this gives more freedom in the sequences and will work perfect.

Thanks for your time!