Extract data from table into different columns

[Danielosama]

Hello everyone,

I have a massive table of data and I wanted to try and extract it in different columns / smaller tables.

The Table consists of Maintenance tasks, their frequency and the items required for it (there are more columns but this is the gist of it).
Some tasks do not have a frequency.

Here is a very reduced version of the Table in question:

Example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Original Table							Wanted result
2	MAINT NAME	Frequency	ITEM				NO DUPLICATES ITEM LIST that have frequency		V (30.000 km)			RI2 (1.080.000 km)
3	A	V (30.000 km)	SCREW1				SCREW1		MAINT NAME	ITEM		MAINT NAME	ITEM
4	B	V (30.000 km)	WASHER1				WASHER1		A	SCREW1		D	SCREW1
5	C	V (30.000 km)	WASHER2				WASHER2		B	WASHER1
6	D	RI2 (1.080.000 km)	SCREW1				SCREW2		H	SCREW1
7	E		SCREW2				SCREW3
8	F	RI (720.000 km)	SCREW2						RI (720.000 km)			RI2 (1.080.000 km)
9	G	2D (360.000 km)	SCREW3						MAINT NAME	ITEM		MAINT NAME	ITEM
10	H	V (30.000 km)	SCREW1						F	SCREW2		G	SCREW3
11
LIST

My first objective is to extract all non-duplicate items that have a frequency as seen in the Non Duplicate items table.

And my second one is to extract the Tasks and their items per Frequency in smaller tables as seen in the different smaller tables.

I have tried with different combinations of INDEX and AGGREGATE to no avail.... I admit this is a bit beyond me :')

Any help would be strongly appreciated.

All best,

 

[Danielosama]

I messed when copying the mini-sheet.... the Top Right Table should be 2D (360.000 km) and not RI2 (1.080.000 km).

Apologies for the confusion.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			Original Table							Wanted result
2		MAINT NAME	Frequency	ITEM				NO DUPLICATES ITEM LIST that have frequency		V (30.000 km)
3		A	V (30.000 km)	SCREW1				SCREW1		MAINT NAME	ITEM
4		B	V (30.000 km)	WASHER1				WASHER1		A	V (30.000 km)
5		C	V (30.000 km)	WASHER2				WASHER2		B	V (30.000 km)
6		D	RI2 (1.080.000 km)	SCREW1				SCREW2		C	V (30.000 km)
7		E		SCREW2				SCREW3		H	V (30.000 km)
8		F	RI (720.000 km)	SCREW2						RI (720.000 km)
9		G	2D (360.000 km)	SCREW3						MAINT NAME	ITEM
10		H	V (30.000 km)	SCREW1						F	RI (720.000 km)
11
12
Data

Cell Formulas
Range	Formula
H3:H7	H3	=UNIQUE(FILTER(D3:D100,C3:C100<>""))
J4:K7	J4	=FILTER(B3:C100,C3:C100=J2)
J10:K10	J10	=FILTER(B3:C100,C3:C100=J8)
Dynamic array formulas.

 

[SanjayGMusafir]

Have you thought of using SLICER on the main data Table?

As of No Duplicates List you can use UNIQUE function as suggested by @Fluff above

 

[Danielosama]

@Fluff thank you very much! That was exactly what I was looking for.

 

[Fluff]

You're welcome & thanks for the feedback.