Extracting Rows Based on a Filter

[G1511]

Hi,

I'd like to extract specific cells from a table like the sample one below based on the sample type in column 1. I have also provided a sample output below. Basically, I want a final table where I can classify some columns based on the types separately. What formulae can I use for achieving such an output?

Sample Data:

Type	Data Point 1	Data Point 2	Data Point 3	Data Point 4	Data Point 5	Data Point 6
A	1	2	3	4	5	6
B	7	8	9	10	11	12
C	13	14	15	16	17	18
D	19	20	21	22	23	24
E	25	26	27	28	29	30
A	31	32	33	34	35	36
C	37	38	39	40	41	42
B	43	44	45	46	47	48
E	49	50	51	52	53	54
D	55	56	57	58	59	60
B	61	62	63	64	65	66
B	67	68	69	70	71	72
A	73	74	75	76	77	78
C	79	80	81	82	83	84
C	85	86	87	88	89	90
C	91	92	93	94	95	96
E	97	98	99	100	101	102

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

Sample Output:

Type A
Data Point 3	Data Point 6	Data Point 1	Data Point 5
Type B
Data Point 3	Data Point 6	Data Point 1	Data Point 5
Type C
Data Point 3	Data Point 6	Data Point 1	Data Point 5
Type D
Data Point 3	Data Point 6	Data Point 1	Data Point 5

<colgroup><col span="4"></colgroup><tbody>
</tbody>

Thanks much in advance!

 

[FDibbins]

You really show what you expect here, just the layout of what you want, but mayb e this?

	A​	B​	C​	D​	E​	F​	G​
1​
2​	Sample Data:
3​	Type	Data Point 1	Data Point 2	Data Point 3	Data Point 4	Data Point 5	Data Point 6
4​	A	1​	2​	3​	4​	5​	6​
5​	B	7​	8​	9​	10​	11​	12​
6​	C	13​	14​	15​	16​	17​	18​
7​	D	19​	20​	21​	22​	23​	24​
8​	E	25​	26​	27​	28​	29​	30​
9​	A	31​	32​	33​	34​	35​	36​
10​	C	37​	38​	39​	40​	41​	42​
11​	B	43​	44​	45​	46​	47​	48​
12​	E	49​	50​	51​	52​	53​	54​
13​	D	55​	56​	57​	58​	59​	60​
14​	B	61​	62​	63​	64​	65​	66​
15​	B	67​	68​	69​	70​	71​	72​
16​	A	73​	74​	75​	76​	77​	78​
17​	C	79​	80​	81​	82​	83​	84​
18​	C	85​	86​	87​	88​	89​	90​
19​	C	91​	92​	93​	94​	95​	96​
20​	E	97​	98​	99​	100​	101​	102​

	J​	K​	L​	M​
2​	Type A
3​	Data Point 3​	Data Point 6​	Data Point 1​	Data Point 5​
4​	3​	6​	1​	5​
5​	​	​	​	​

J4=IFERROR(INDEX($B$4:$G$20,MATCH(RIGHT($J$2,1),$A$4:$A$20,0),MATCH(J$3,$B$3:$G$3,0)),"")
copied across.

Change the reference in RIGHT($J$2,1) for the other tables

 

[G1511]

You really show what you expect here, just the layout of what you want, but mayb e this?

	A​	B​	C​	D​	E​	F​	G​
1​
2​	Sample Data:
3​	Type	Data Point 1	Data Point 2	Data Point 3	Data Point 4	Data Point 5	Data Point 6
4​	A	1​	2​	3​	4​	5​	6​
5​	B	7​	8​	9​	10​	11​	12​
6​	C	13​	14​	15​	16​	17​	18​
7​	D	19​	20​	21​	22​	23​	24​
8​	E	25​	26​	27​	28​	29​	30​
9​	A	31​	32​	33​	34​	35​	36​
10​	C	37​	38​	39​	40​	41​	42​
11​	B	43​	44​	45​	46​	47​	48​
12​	E	49​	50​	51​	52​	53​	54​
13​	D	55​	56​	57​	58​	59​	60​
14​	B	61​	62​	63​	64​	65​	66​
15​	B	67​	68​	69​	70​	71​	72​
16​	A	73​	74​	75​	76​	77​	78​
17​	C	79​	80​	81​	82​	83​	84​
18​	C	85​	86​	87​	88​	89​	90​
19​	C	91​	92​	93​	94​	95​	96​
20​	E	97​	98​	99​	100​	101​	102​

<tbody>
</tbody>

	J​	K​	L​	M​
2​	Type A
3​	Data Point 3​	Data Point 6​	Data Point 1​	Data Point 5​
4​	3​	6​	1​	5​
5​

<tbody>
</tbody>

J4=IFERROR(INDEX($B$4:$G$20,MATCH(RIGHT($J$2,1),$A$4:$A$20,0),MATCH(J$3,$B$3:$G$3,0)),"")
copied across.

Change the reference in RIGHT($J$2,1) for the other tables

Hi FDibbins, thank you for your response!

I'm just showing the layout of my data since the data I have is confidential.

The solution you have given works if the values in Column 1 (Type) were unique but they are repetitive. So the result of your formula will give me the first value for Type A but I'd like a consolidated list of all the Type A values. Almost like what a PivotTable provides but I don't want to use Pivots.

Is there a solution for that?

Thanks!

 

[FDibbins]

So you want the repeats summed? That's why I asked for some sample answers

 

[G1511]

So you want the repeats summed? That's why I asked for some sample answers

Yes, I need a summary table. Sorry for not giving out the answers earlier. I have added the same below. These I created using a Pivot and then breaking it apart and formatting it.

Type A
Data Point 2	Data Point 3	Data Point 5
2	3	5
32	33	35
74	75	77
Type B
8	9	11
44	45	47
62	63	65
68	69	71
Type C
14	15	17
38	39	41
80	81	83
86	87	89
92	93	95
Type D
20	21	23
56	57	59
Type E
26	27	29
50	51	53
98	99	101

<colgroup><col span="3"></colgroup><tbody>
</tbody>