Remove Duplicate Entries from List

[justanotheruser]

Hi all,

I've got a drop down list which is linked to a source sheet called "Mapping", and on this sheet I have selected my range for Data Validation for a Drop Down list.

However, there are certain values which are repeated, so I've used some IF, COUNTIF, ROWS and INDEX formulas to sort it out and remove duplicate entries.

In the table, below, the data is stored in Column C, the first part of my calculations is in Column A, and then the second part is in Column B - which I want to use as the source for my drop down list. The only problem that I have is that instead of blanks cells there are 0s in the cell as below. Could someone help me fix it? (cell C2 is intentionally blank)

Excel Workbook
	A	B	C
1	*	*	Entity
2	*	*	*
3	1	210	210
4	1	220	210
5	1	230	210
6	2	240	220
7	2	250	220
8	3	0	230
9	3	0	230
10	3	0	230
11	4	0	240
12	5	0	250
13	6	0	*
14	7	0	*
15	8	0	*
16	9	0	*
17	10	0	*
18	11	0	*
19	12	0	*
20	13	0	*
21	14	*	*
22	15	*	*
23	16	*	*
24	17	*	*
25	18	*	*
26	*	*	*
27	*	*	*
Mapping

Thanks in advance.

 

[justanotheruser]

I don't know if this is possible, but if I select the range B2:B27 in this case if the 0s were blank cells, would it be able to show just the 5 values that aren't blank/0 i.e. cells B3:B7 in this instance?

Thanks again in advance for your help!

 

[snoopyhr]

is this waht you are after?

Assuming your list in number, if not use counta( ) and subtracts number of rows that are not relevent to the list. Example

Row 1 = Title
Row 2 start of the table
then counta(a:a)-1 etc.

Excel Workbook
	A	B	C	D	E
1	*	*	Entity	*	*
2	*	*	*	*	Unique list and sorted
3	1	210	210	*	210
4	1	220	210	*	220
5	1	230	210	*	230
6	2	240	220	*	240
7	2	250	220	*	250
8	3	0	230	*	*
9	3	0	230	*	*
10	3	0	230	*	*
11	4	0	240	*	*
12	5	0	250	*	*
13	6	0	*	*	*
14	7	0	*	*	*
15	8	0	*	*	*
16	9	0	*	*	*
17	10	0	*	*	*
18	11	0	*	*	*
19	12	0	*	*	*
20	13	0	*	*	*
21	14	*	*	*	*
22	15	*	*	*	*
23	16	*	*	*	*
24	17	*	*	*	*
25	18	*	*	*	*
26	*	*	*	*	*
27	*	*	*	*	*
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[justanotheruser]

Unfortunately no, the Entitys will not only be numbers, say for example (I just made these up), these will be as follows: I've copied the full sheet below for you.

Excel Workbook
	A	B	C	D
1			Entity	Sheet Name
2
3	1	FA6_210	FA6_210	PL_Workwear
4	1	FA7_220	FA6_210	BS_Workwear
5	1	FA8S_220	FA6_210	PL_Cleanroom
6	2	FA89_230	FA7_220	PL_Workwear
7	3	KK_230	FA8S_220	BS_Workwear
8	4	ZE_240	FA89_230	PL_Cleanroom
9	5	FA78_250	KK_230	PL_Workwear
10	5	0	KK_230	BS_Workwear
11	6	0	ZE_240
12	7	0	FA78_250
13	8	0
14	9	0
15	10	0
16	11	0
17	12	0
18	13	0
19	14	0
20	15	0
21	16	0
22	17	0
23	18
24	19
25	20
Mapping

Thanks.

 

[Marcelo Branco]

Maybe using a Custom format in column B like

[=0]""

M.

 

[justanotheruser]

Maybe using a Custom format in column B like

[=0]""

M.

Thanks Marcelo - that works great - can I create a dynamic range for Column B so that it only selects cells where there is a value in the cell (i.e. ignores the blank cells?)

Excel Workbook
	A	B	C	D
1	*	*	Entity	Sheet Name
2	*	*	*	*
3	1	FA6_210	FA6_210	PL_Workwear
4	1	FA7_220	FA6_210	BS_Workwear
5	1	FA8S_220	FA6_210	PL_Cleanroom
6	2	FA89_230	FA7_220	PL_Workwear
7	3	KK_230	FA8S_220	BS_Workwear
8	4	ZE_240	FA89_230	PL_Cleanroom
9	5	FA78_250	KK_230	PL_Workwear
10	5	*	KK_230	BS_Workwear
11	6	*	ZE_240	*
12	7	*	FA78_250	*
Mapping

Thanks again

 

[snoopyhr]

see if it works for you.

Excel Workbook
	A	B	C	D	E	F	G
1			Entity	Sheet Name			Unique list and sorted
2	1	FA6_210	FA6_210	PL_Workwear			FA6_210
3	1	FA7_220	FA6_210	BS_Workwear			FA7_220
4	1	FA8S_220	FA6_210	PL_Cleanroom			FA78_250
5	2	FA89_230	FA7_220	PL_Workwear			FA89_230
6	3	KK_230	FA8S_220	BS_Workwear			FA8S_220
7	4	ZE_240	FA89_230	PL_Cleanroom			KK_230
8	5	FA78_250	KK_230	PL_Workwear			ZE_240
9	5	0	KK_230	BS_Workwear
10	6	0	ZE_240
11	7	0	FA78_250
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[justanotheruser]

Apologies for my newbieness, but how do I add Names in Formulas (the "Applies to: =OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C:$C)-1)" bit?)?

 

[snoopyhr]

Excel 2003
Click on INSERT, Names, Define name

Name in the workbook box , enter the desired name
Refers to : copy the formula in that box, just make sure you reference the range correctly

That is , if the Entity in in column C and data starts in row 2 then the formula for the range will be:
=offset($C$2,,,counta($c:$C)-1)

hope it helps

 

[justanotheruser]

Hi,

Thank you for your response - I've tried the code in my spreadsheet, but it doesn't work as row 2 needs to contain a blank cell (i.e. blank as the initial starting cell for the dropdown list) so I think this might be causing it to break:

Excel Workbook
	A	B	C	D	E	F	G
1			Entity	Sheet Name			Unique list
2
3	1	FA6_210	FA6_210	PL_Workwear
4	1	FA7_220	FA6_210	BS_Workwear
5	1	FA8S_220	FA6_210	PL_Cleanroom
6	2	FA89_230	FA7_220	PL_Workwear
7	3	KK_230	FA8S_220	BS_Workwear
8	4	ZE_240	FA89_230	PL_Cleanroom
9	5	FA78_250	KK_230	PL_Workwear
10	5		KK_230	BS_Workwear
11	6		ZE_240
12	7		FA78_250
Mapping

Can the formula be adjusted to allow the first blank? Thanks