Unique values from multiple columns

[ravi2628]

Dear Friends,

Good Morning, Afternoon, Night.

I was looking for a formula to extract the unique values from different columns in a excel.

input:

Book1
	A	B	C	D	E	F	G	H
1	Consider	Invoice Number	Division	Bill Date	Month	HQ	Purchase Order	Material Description
2	x	912967556	Cytotoxic	14-04-2023	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL
3	x	912967556	Cytotoxic	14-04-2023	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL
4	x	912967557	Cytotoxic	14-04-2023	April	Nashik	EMAIL:-	KETSIA 50MG/ML 10ML VIAL
5	x	912967558	Cytotoxic	20-04-2023	April	Kurnool	EMAIL:-	VIBRO 4 MG
6	x	912967558	Cytotoxic	20-04-2023	April	Kurnool	EMAIL:-	VIBRO 4 MG
7	x	912967559	Cytotoxic	22-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG
8	x	912967559	Cytotoxic	22-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG
9	x	912967559	Cytotoxic	22-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG
10	x	912967561	MABS	24-04-2023	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)
11	x	912967561	MABS	24-04-2023	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)
12	x	912967562	MABS	20-04-2023	April	Delhi	order	CELOCORT 100 MG (25’S)
13	x	912967563	MABS	28-04-2023	April	Vijayawada	EMAIL:-	MIBEN 1MG/ML 10ML
14	x	912967564	MABS	28-04-2023	April	Vijayawada	EMAIL:-	KETSIA 50MG/ML 10ML VIAL
15	x	912967565	MABS	28-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG
16	x	912967565	MABS	28-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG
17	x	912967566	Cytotoxic	28-04-2023	April	Jalgaon	EMAIL:-	KETSIA 50MG/ML 10ML VIAL
18		912967567	MABS	21-04-2023	April	Guntur	EMAIL:-	ENCOXEL 40 MG (2’S)
19	x	912967568	Cytotoxic	24-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
20	x	912967568	Cytotoxic	24-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
21	x	912967568	Cytotoxic	24-04-2023	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
22	x	912967568	Cytotoxic	24-04-2023	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)
23	x	912967568	Cytotoxic	24-04-2023	April	Hyderabad	EMAIL:-	ENCOXEL 20 MG (2’S)
24	x	912967569	Cytotoxic	26-04-2023	April	Trivandrum	EMAIL:-	TACHYDON 150 MG INJ
25		912967571	MABS	28-04-2023	April	Vijayawada	EMAIL:-	CELODATE 20 MG
26		912967571	MABS	28-04-2023	April	Vijayawada	EMAIL:-	CELODATE 20 MG
27		912967571	MABS	28-04-2023	April	Vijayawada	EMAIL:-	CELODATE 20 MG
28		912967571	MABS	28-04-2023	April	Vijayawada	EMAIL:-	CELODATE 20 MG
29		912967571	MABS	28-04-2023	April	Vijayawada	EMAIL:-	CELODATE 20 MG
30	x	912967572	Cytotoxic	29-04-2023	April	Hyderabad	EMAIL:-	VASCEL 20MG
31	x	912967572	Cytotoxic	29-04-2023	April	Hyderabad	EMAIL:-	VASCEL 20MG
32	x	912967573	MABS	29-04-2023	April	Karimnagar	EMAIL:-	TRAMCEL 50
33	x	912967574	MABS	29-04-2023	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
34	x	912967574	MABS	29-04-2023	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
35	x	912967575	Cytotoxic	24-04-2023	April	Nellore	EMAIL:-	CELOPRED 40MG
36	x	912967575	Cytotoxic	24-04-2023	April	Nellore	EMAIL:-	CELOPRED 40MG
37	x	912967575	Cytotoxic	24-04-2023	April	Nellore	EMAIL:-	CELOPRED 40MG
38	x	912967575	Cytotoxic	26-04-2023	April	Nellore	EMAIL:-	CELOPRED 40MG
39	x	912967575	Cytotoxic	28-04-2023	April	Nellore	EMAIL:-	CELOPRED 40MG
Sheet1

Output:

Book1
	A	B	C
1	Filter Condition on Column A:		X
2
3
4	Division	HQ	Material Description
5	Cytotoxic	Pune	KETSIA 50MG/ML 10ML VIAL
6	Cytotoxic	Nashik	KETSIA 50MG/ML 10ML VIAL
7	Cytotoxic	Kurnool	VIBRO 4 MG
8	Cytotoxic	Hyderabad	HYPROVAN MELT 100 MG
9	MABS	Hyderabad	LABIL 20MG (AMPOULE)
10	MABS	Delhi	CELOCORT 100 MG (25’S)
11	MABS	Vijayawada	MIBEN 1MG/ML 10ML
12	MABS	Vijayawada	KETSIA 50MG/ML 10ML VIAL
13	MABS	Hyderabad	HYPROVAN 100 MG
14	Cytotoxic	Jalgaon	KETSIA 50MG/ML 10ML VIAL
15	Cytotoxic	Hyderabad	HYPROVAN 200 MG
16	Cytotoxic	Hyderabad	LABIL 20MG (AMPOULE)
17	Cytotoxic	Hyderabad	ENCOXEL 20 MG (2’S)
18	Cytotoxic	Trivandrum	TACHYDON 150 MG INJ
19	Cytotoxic	Hyderabad	VASCEL 20MG
20	MABS	Karimnagar	TRAMCEL 50
21	MABS	Srinagar	SEDETO 100 MCG (1 X 5’S)
22	Cytotoxic	Nellore	CELOPRED 40MG
Sheet2

Filter on Column A.
Unique values from C,F,H

Please help me out with the logic.

Thanks in advance.

Regards,
Ravi

 

[Peter_SSs]

Like this?

ravi2628.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Consider	Invoice Number	Division	Bill Date	Month	HQ	Purchase Order	Material Description		Division	HQ	Material Description
2	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Pune	KETSIA 50MG/ML 10ML VIAL
3	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Nashik	KETSIA 50MG/ML 10ML VIAL
4	x	912967557	Cytotoxic	45030	April	Nashik	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Kurnool	VIBRO 4 MG
5	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		Cytotoxic	Hyderabad	HYPROVAN MELT 100 MG
6	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		MABS	Hyderabad	LABIL 20MG (AMPOULE)
7	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Delhi	CELOCORT 100 MG (25’S)
8	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	MIBEN 1MG/ML 10ML
9	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	KETSIA 50MG/ML 10ML VIAL
10	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		MABS	Hyderabad	HYPROVAN 100 MG
11	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		Cytotoxic	Jalgaon	KETSIA 50MG/ML 10ML VIAL
12	x	912967562	MABS	45036	April	Delhi	order	CELOCORT 100 MG (25’S)		Cytotoxic	Hyderabad	HYPROVAN 200 MG
13	x	912967563	MABS	45044	April	Vijayawada	EMAIL:-	MIBEN 1MG/ML 10ML		Cytotoxic	Hyderabad	LABIL 20MG (AMPOULE)
14	x	912967564	MABS	45044	April	Vijayawada	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Hyderabad	ENCOXEL 20 MG (2’S)
15	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Trivandrum	TACHYDON 150 MG INJ
16	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Hyderabad	VASCEL 20MG
17	x	912967566	Cytotoxic	45044	April	Jalgaon	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		MABS	Karimnagar	TRAMCEL 50
18		912967567	MABS	45037	April	Guntur	EMAIL:-	ENCOXEL 40 MG (2’S)		MABS	Srinagar	SEDETO 100 MCG (1 X 5’S)
19	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG		Cytotoxic	Nellore	CELOPRED 40MG
20	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
21	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
22	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)
23	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	ENCOXEL 20 MG (2’S)
24	x	912967569	Cytotoxic	45042	April	Trivandrum	EMAIL:-	TACHYDON 150 MG INJ
25		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
26		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
27		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
28		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
29		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
30	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG
31	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG
32	x	912967573	MABS	45045	April	Karimnagar	EMAIL:-	TRAMCEL 50
33	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
34	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
35	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
36	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
37	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
38	x	912967575	Cytotoxic	45042	April	Nellore	EMAIL:-	CELOPRED 40MG
39	x	912967575	Cytotoxic	45044	April	Nellore	EMAIL:-	CELOPRED 40MG
Sheet1

Cell Formulas
Range	Formula
J1:L19	J1	=UNIQUE(CHOOSECOLS(FILTER(A1:H39,A1:A39<>""),3,6,8))
Dynamic array formulas.

 

[ravi2628]

Like this?

ravi2628.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Consider	Invoice Number	Division	Bill Date	Month	HQ	Purchase Order	Material Description		Division	HQ	Material Description
2	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Pune	KETSIA 50MG/ML 10ML VIAL
3	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Nashik	KETSIA 50MG/ML 10ML VIAL
4	x	912967557	Cytotoxic	45030	April	Nashik	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Kurnool	VIBRO 4 MG
5	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		Cytotoxic	Hyderabad	HYPROVAN MELT 100 MG
6	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		MABS	Hyderabad	LABIL 20MG (AMPOULE)
7	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Delhi	CELOCORT 100 MG (25’S)
8	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	MIBEN 1MG/ML 10ML
9	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	KETSIA 50MG/ML 10ML VIAL
10	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		MABS	Hyderabad	HYPROVAN 100 MG
11	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		Cytotoxic	Jalgaon	KETSIA 50MG/ML 10ML VIAL
12	x	912967562	MABS	45036	April	Delhi	order	CELOCORT 100 MG (25’S)		Cytotoxic	Hyderabad	HYPROVAN 200 MG
13	x	912967563	MABS	45044	April	Vijayawada	EMAIL:-	MIBEN 1MG/ML 10ML		Cytotoxic	Hyderabad	LABIL 20MG (AMPOULE)
14	x	912967564	MABS	45044	April	Vijayawada	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Hyderabad	ENCOXEL 20 MG (2’S)
15	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Trivandrum	TACHYDON 150 MG INJ
16	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Hyderabad	VASCEL 20MG
17	x	912967566	Cytotoxic	45044	April	Jalgaon	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		MABS	Karimnagar	TRAMCEL 50
18		912967567	MABS	45037	April	Guntur	EMAIL:-	ENCOXEL 40 MG (2’S)		MABS	Srinagar	SEDETO 100 MCG (1 X 5’S)
19	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG		Cytotoxic	Nellore	CELOPRED 40MG
20	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
21	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG
22	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)
23	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	ENCOXEL 20 MG (2’S)
24	x	912967569	Cytotoxic	45042	April	Trivandrum	EMAIL:-	TACHYDON 150 MG INJ
25		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
26		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
27		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
28		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
29		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
30	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG
31	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG
32	x	912967573	MABS	45045	April	Karimnagar	EMAIL:-	TRAMCEL 50
33	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
34	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)
35	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
36	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
37	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG
38	x	912967575	Cytotoxic	45042	April	Nellore	EMAIL:-	CELOPRED 40MG
39	x	912967575	Cytotoxic	45044	April	Nellore	EMAIL:-	CELOPRED 40MG
Sheet1

Cell Formulas
Range	Formula
J1:L19	J1	=UNIQUE(CHOOSECOLS(FILTER(A1:H39,A1:A39<>""),3,6,8))
Dynamic array formulas.

Thanks Peter it was working perfectly for 365 but how to use in 2016 or 2019 because some of our device still have older versions

 

[Peter_SSs]

.. how to use in 2016 or 2019 because some of our device still have older versions

If version is important, please specify that in your opening post so helpers don't waste their time developing a suggestion that will not be accepted.

I would use helper columns. I have used N:O and these could later be hidden.

ravi2628.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Consider	Invoice Number	Division	Bill Date	Month	HQ	Purchase Order	Material Description
2	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Pune	KETSIA 50MG/ML 10ML VIAL		1	|Cytotoxic|Pune|KETSIA 50MG/ML 10ML VIAL|
3	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Nashik	KETSIA 50MG/ML 10ML VIAL		2	|Cytotoxic|Pune|KETSIA 50MG/ML 10ML VIAL|
4	x	912967557	Cytotoxic	45030	April	Nashik	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Kurnool	VIBRO 4 MG		1	|Cytotoxic|Nashik|KETSIA 50MG/ML 10ML VIAL|
5	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		Cytotoxic	Hyderabad	HYPROVAN MELT 100 MG		1	|Cytotoxic|Kurnool|VIBRO 4 MG|
6	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		MABS	Hyderabad	LABIL 20MG (AMPOULE)		2	|Cytotoxic|Kurnool|VIBRO 4 MG|
7	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Delhi	CELOCORT 100 MG (25’S)		1	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
8	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	MIBEN 1MG/ML 10ML		2	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
9	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	KETSIA 50MG/ML 10ML VIAL		3	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
10	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		MABS	Hyderabad	HYPROVAN 100 MG		1	|MABS|Hyderabad|LABIL 20MG (AMPOULE)|
11	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		Cytotoxic	Jalgaon	KETSIA 50MG/ML 10ML VIAL		2	|MABS|Hyderabad|LABIL 20MG (AMPOULE)|
12	x	912967562	MABS	45036	April	Delhi	order	CELOCORT 100 MG (25’S)		Cytotoxic	Hyderabad	HYPROVAN 200 MG		1	|MABS|Delhi|CELOCORT 100 MG (25’S)|
13	x	912967563	MABS	45044	April	Vijayawada	EMAIL:-	MIBEN 1MG/ML 10ML		Cytotoxic	Hyderabad	LABIL 20MG (AMPOULE)		1	|MABS|Vijayawada|MIBEN 1MG/ML 10ML|
14	x	912967564	MABS	45044	April	Vijayawada	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Hyderabad	ENCOXEL 20 MG (2’S)		1	|MABS|Vijayawada|KETSIA 50MG/ML 10ML VIAL|
15	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Trivandrum	TACHYDON 150 MG INJ		1	|MABS|Hyderabad|HYPROVAN 100 MG|
16	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Hyderabad	VASCEL 20MG		2	|MABS|Hyderabad|HYPROVAN 100 MG|
17	x	912967566	Cytotoxic	45044	April	Jalgaon	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		MABS	Karimnagar	TRAMCEL 50		1	|Cytotoxic|Jalgaon|KETSIA 50MG/ML 10ML VIAL|
18		912967567	MABS	45037	April	Guntur	EMAIL:-	ENCOXEL 40 MG (2’S)		MABS	Srinagar	SEDETO 100 MCG (1 X 5’S)
19	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG		Cytotoxic	Nellore	CELOPRED 40MG		1	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
20	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG						2	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
21	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG						3	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
22	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)						1	|Cytotoxic|Hyderabad|LABIL 20MG (AMPOULE)|
23	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	ENCOXEL 20 MG (2’S)						1	|Cytotoxic|Hyderabad|ENCOXEL 20 MG (2’S)|
24	x	912967569	Cytotoxic	45042	April	Trivandrum	EMAIL:-	TACHYDON 150 MG INJ						1	|Cytotoxic|Trivandrum|TACHYDON 150 MG INJ|
25		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
26		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
27		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
28		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
29		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
30	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG						1	|Cytotoxic|Hyderabad|VASCEL 20MG|
31	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG						2	|Cytotoxic|Hyderabad|VASCEL 20MG|
32	x	912967573	MABS	45045	April	Karimnagar	EMAIL:-	TRAMCEL 50						1	|MABS|Karimnagar|TRAMCEL 50|
33	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)						1	|MABS|Srinagar|SEDETO 100 MCG (1 X 5’S)|
34	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)						2	|MABS|Srinagar|SEDETO 100 MCG (1 X 5’S)|
35	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						1	|Cytotoxic|Nellore|CELOPRED 40MG|
36	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						2	|Cytotoxic|Nellore|CELOPRED 40MG|
37	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						3	|Cytotoxic|Nellore|CELOPRED 40MG|
38	x	912967575	Cytotoxic	45042	April	Nellore	EMAIL:-	CELOPRED 40MG						4	|Cytotoxic|Nellore|CELOPRED 40MG|
39	x	912967575	Cytotoxic	45044	April	Nellore	EMAIL:-	CELOPRED 40MG						5	|Cytotoxic|Nellore|CELOPRED 40MG|
Sheet3

Cell Formulas
Range	Formula
J2:J23	J2	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$39)/($N$2:$N$39=1),ROWS(J$2:J2))),"")
K2:K23	K2	=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$39)/($N$2:$N$39=1),ROWS(K$2:K2))),"")
L2:L23	L2	=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW(H$2:H$39)/($N$2:$N$39=1),ROWS(L$2:L2))),"")
N2:N39	N2	=IF(O2="","",COUNTIF(O$2:O2,O2))
O2:O39	O2	=IF(A2="","","|"&C2&"|"&F2&"|"&H2&"|")

 

[ravi2628]

If version is important, please specify that in your opening post so helpers don't waste their time developing a suggestion that will not be accepted.

I would use helper columns. I have used N:O and these could later be hidden.

ravi2628.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Consider	Invoice Number	Division	Bill Date	Month	HQ	Purchase Order	Material Description
2	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Pune	KETSIA 50MG/ML 10ML VIAL		1	|Cytotoxic|Pune|KETSIA 50MG/ML 10ML VIAL|
3	x	912967556	Cytotoxic	45030	April	Pune	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Nashik	KETSIA 50MG/ML 10ML VIAL		2	|Cytotoxic|Pune|KETSIA 50MG/ML 10ML VIAL|
4	x	912967557	Cytotoxic	45030	April	Nashik	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Kurnool	VIBRO 4 MG		1	|Cytotoxic|Nashik|KETSIA 50MG/ML 10ML VIAL|
5	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		Cytotoxic	Hyderabad	HYPROVAN MELT 100 MG		1	|Cytotoxic|Kurnool|VIBRO 4 MG|
6	x	912967558	Cytotoxic	45036	April	Kurnool	EMAIL:-	VIBRO 4 MG		MABS	Hyderabad	LABIL 20MG (AMPOULE)		2	|Cytotoxic|Kurnool|VIBRO 4 MG|
7	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Delhi	CELOCORT 100 MG (25’S)		1	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
8	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	MIBEN 1MG/ML 10ML		2	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
9	x	912967559	Cytotoxic	45038	April	Hyderabad	EMAIL:-	HYPROVAN MELT 100 MG		MABS	Vijayawada	KETSIA 50MG/ML 10ML VIAL		3	|Cytotoxic|Hyderabad|HYPROVAN MELT 100 MG|
10	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		MABS	Hyderabad	HYPROVAN 100 MG		1	|MABS|Hyderabad|LABIL 20MG (AMPOULE)|
11	x	912967561	MABS	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)		Cytotoxic	Jalgaon	KETSIA 50MG/ML 10ML VIAL		2	|MABS|Hyderabad|LABIL 20MG (AMPOULE)|
12	x	912967562	MABS	45036	April	Delhi	order	CELOCORT 100 MG (25’S)		Cytotoxic	Hyderabad	HYPROVAN 200 MG		1	|MABS|Delhi|CELOCORT 100 MG (25’S)|
13	x	912967563	MABS	45044	April	Vijayawada	EMAIL:-	MIBEN 1MG/ML 10ML		Cytotoxic	Hyderabad	LABIL 20MG (AMPOULE)		1	|MABS|Vijayawada|MIBEN 1MG/ML 10ML|
14	x	912967564	MABS	45044	April	Vijayawada	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		Cytotoxic	Hyderabad	ENCOXEL 20 MG (2’S)		1	|MABS|Vijayawada|KETSIA 50MG/ML 10ML VIAL|
15	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Trivandrum	TACHYDON 150 MG INJ		1	|MABS|Hyderabad|HYPROVAN 100 MG|
16	x	912967565	MABS	45044	April	Hyderabad	EMAIL:-	HYPROVAN 100 MG		Cytotoxic	Hyderabad	VASCEL 20MG		2	|MABS|Hyderabad|HYPROVAN 100 MG|
17	x	912967566	Cytotoxic	45044	April	Jalgaon	EMAIL:-	KETSIA 50MG/ML 10ML VIAL		MABS	Karimnagar	TRAMCEL 50		1	|Cytotoxic|Jalgaon|KETSIA 50MG/ML 10ML VIAL|
18		912967567	MABS	45037	April	Guntur	EMAIL:-	ENCOXEL 40 MG (2’S)		MABS	Srinagar	SEDETO 100 MCG (1 X 5’S)
19	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG		Cytotoxic	Nellore	CELOPRED 40MG		1	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
20	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG						2	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
21	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	HYPROVAN 200 MG						3	|Cytotoxic|Hyderabad|HYPROVAN 200 MG|
22	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	LABIL 20MG (AMPOULE)						1	|Cytotoxic|Hyderabad|LABIL 20MG (AMPOULE)|
23	x	912967568	Cytotoxic	45040	April	Hyderabad	EMAIL:-	ENCOXEL 20 MG (2’S)						1	|Cytotoxic|Hyderabad|ENCOXEL 20 MG (2’S)|
24	x	912967569	Cytotoxic	45042	April	Trivandrum	EMAIL:-	TACHYDON 150 MG INJ						1	|Cytotoxic|Trivandrum|TACHYDON 150 MG INJ|
25		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
26		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
27		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
28		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
29		912967571	MABS	45044	April	Vijayawada	EMAIL:-	CELODATE 20 MG
30	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG						1	|Cytotoxic|Hyderabad|VASCEL 20MG|
31	x	912967572	Cytotoxic	45045	April	Hyderabad	EMAIL:-	VASCEL 20MG						2	|Cytotoxic|Hyderabad|VASCEL 20MG|
32	x	912967573	MABS	45045	April	Karimnagar	EMAIL:-	TRAMCEL 50						1	|MABS|Karimnagar|TRAMCEL 50|
33	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)						1	|MABS|Srinagar|SEDETO 100 MCG (1 X 5’S)|
34	x	912967574	MABS	45045	April	Srinagar	EMAIL:-	SEDETO 100 MCG (1 X 5’S)						2	|MABS|Srinagar|SEDETO 100 MCG (1 X 5’S)|
35	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						1	|Cytotoxic|Nellore|CELOPRED 40MG|
36	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						2	|Cytotoxic|Nellore|CELOPRED 40MG|
37	x	912967575	Cytotoxic	45040	April	Nellore	EMAIL:-	CELOPRED 40MG						3	|Cytotoxic|Nellore|CELOPRED 40MG|
38	x	912967575	Cytotoxic	45042	April	Nellore	EMAIL:-	CELOPRED 40MG						4	|Cytotoxic|Nellore|CELOPRED 40MG|
39	x	912967575	Cytotoxic	45044	April	Nellore	EMAIL:-	CELOPRED 40MG						5	|Cytotoxic|Nellore|CELOPRED 40MG|
Sheet3

Cell Formulas
Range	Formula
J2:J23	J2	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$39)/($N$2:$N$39=1),ROWS(J$2:J2))),"")
K2:K23	K2	=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$39)/($N$2:$N$39=1),ROWS(K$2:K2))),"")
L2:L23	L2	=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW(H$2:H$39)/($N$2:$N$39=1),ROWS(L$2:L2))),"")
N2:N39	N2	=IF(O2="","",COUNTIF(O$2:O2,O2))
O2:O39	O2	=IF(A2="","","|"&C2&"|"&F2&"|"&H2&"|")

Thank you peter & also sorry for not mentioning the version earlier