Finding value inside a cell, returning it & checking for unique entries

[MurdochQuill]

Hi,

I'm trying to sort unique entries in a list of data. Currently I know how to find what I want, which can be done by the following formula:

(INT(MID(F8,SEARCH(", ",A2)+2,LEN(A2)-SEARCH(", ",A2)-4)

However I want to configure it to find the value in column A and paste it in column C, but with no duplicate values. So it would need to search a range of C2:C1000 for any duplicate values. Any help making this formula would be very appreciated.

Here's an example output as well:

Book1
	A	B	C
1			Unique ID no blanks
2	FDSGDF 30 (CVNBFG, 556) A		556
3	DFGDFG-X 2456 (DFHGJ, 767) B		767
4	EK0DFGH25 (DGJYT, 556) C		884
5	DFGHF (YDJFDJ, 556) E
6	SGFH 2 (KTYUK, 556) U
7	HHH 2 (KUTYK, 884) G
8	EFGHS 2 (GFJYT, 767) H
9	GFDNCV 2 (FJY, 556) V
10	XCVB 2 (JHK, 556) S
11	XCVB 2 (YRTU, 556) Z
Sheet1

 

[MurdochQuill]

Sorry. Missed my editing window..

Because the list is going to be filtered, the output will need blanks inbetween until the unique value is found on the same row, and will need to land on the first instance of this unique number EG:

Book1
	A	B	C
1			Unique ID no blanks
2	FDSGDF 30 (CVNBFG, 556) A		556
3	DFGDFG-X 2456 (DFHGJ, 767) B		767
4	EK0DFGH25 (DGJYT, 556) C
5	DFGHF (YDJFDJ, 556) E
6	SGFH 2 (KTYUK, 556) U
7	HHH 2 (KUTYK, 884) G		884
8	EFGHS 2 (GFJYT, 767) H
9	GFDNCV 2 (FJY, 556) V
10	XCVB 2 (JHK, 556) S
11	XCVB 2 (YRTU, 556) Z
Sheet1

 

[MurdochQuill]

Bump, anyone know how to make this formula?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1			Unique ID
2	FDSGDF 30 (CVNBFG, 556) A		556
3	DFGDFG-X 2456 (DFHGJ, 767) B		767
4	EK0DFGH25 (DGJYT, 556) C
5	DFGHF (YDJFDJ, 556) E
6	SGFH 2 (KTYUK, 556) U
7	HHH 2 (KUTYK, 884) G		884
8	EFGHS 2 (GFJYT, 767) H
9	GFDNCV 2 (FJY, 556) V
10	XCVB 2 (JHK, 556) S
11	XCVB 2 (YRTU, 556) Z
Main

Cell Formulas
Range	Formula
C2:C11	C2	=LET(Sp,FIND(", ",A2),Num,INT(MID(A2,Sp+2,LEN(A2)-Sp-4)),IF(COUNTIFS(C$1:C1,Num),"",Num))

 

[MurdochQuill]

How about

+Fluff 1.xlsm
	A	B	C
1			Unique ID
2	FDSGDF 30 (CVNBFG, 556) A		556
3	DFGDFG-X 2456 (DFHGJ, 767) B		767
4	EK0DFGH25 (DGJYT, 556) C
5	DFGHF (YDJFDJ, 556) E
6	SGFH 2 (KTYUK, 556) U
7	HHH 2 (KUTYK, 884) G		884
8	EFGHS 2 (GFJYT, 767) H
9	GFDNCV 2 (FJY, 556) V
10	XCVB 2 (JHK, 556) S
11	XCVB 2 (YRTU, 556) Z
Main

Cell Formulas
Range	Formula
C2:C11	C2	=LET(Sp,FIND(", ",A2),Num,INT(MID(A2,Sp+2,LEN(A2)-Sp-4)),IF(COUNTIFS(C$1:C1,Num),"",Num))

Thanks mate. I think I owe you a few beers by now.

 

[Fluff]

You're welcome & thanks for the feedback.