Find Single Value in Column

[SOLTEC]

Excel 2010
	D	E
1		Product Name
2	1	Replacement Boot Laces
3	1	Replacement Boot Laces
4	3	Blousing Garters
5	3	Blousing Garters
6	3	Blousing Garters
7	3	Blousing Garters
8	7	GI Extreme Cold Weather Bunny Boots
9	7	GI Extreme Cold Weather Bunny Boots
10	7	GI Extreme Cold Weather Bunny Boots
11	7	GI Extreme Cold Weather Bunny Boots
12	7	GI Extreme Cold Weather Bunny Boots
13	7	GI Extreme Cold Weather Bunny Boots
14	13	Nylon Commando Wallet
15	13	Nylon Commando Wallet
16	13	Nylon Commando Wallet
17	13	Nylon Commando Wallet
18	13	Nylon Commando Wallet
19	13	Nylon Commando Wallet
20	13	Nylon Commando Wallet
21	20	Pepper Sprays Gel Sabre Red
22	21	Fire Master Fog Pepper Spray
23	20	Pepper Sprays Gel Sabre Red
24	20	Pepper Sprays Gel Sabre Red
25	20	Pepper Sprays Gel Sabre Red
26	20	Pepper Sprays Gel Sabre Red
27	26	Key Chain Pepper Spray
28	20	Pepper Sprays Gel Sabre Red
29	28	Cop Top Fog Cone Pepper Spray
30	29	Flip Top Stream Pepper Spray
31	30	Flip Top Cop Med Fog Cone Pepper Spray
32	20	Pepper Sprays Gel Sabre Red
33	32	Cop Top Stream Pepper Spray
34	20	Pepper Sprays Gel Sabre Red
35	28	Cop Top Fog Cone Pepper Spray
36	29	Flip Top Stream Pepper Spray
37	30	Flip Top Cop Med Fog Cone Pepper Spray
38	32	Cop Top Stream Pepper Spray
39	20	Pepper Sprays Gel Sabre Red
40	20	Pepper Sprays Gel Sabre Red
41	40	Police Magnum Sabre Red
42	20	Pepper Sprays Gel Sabre Red
Workspace

Cell Formulas
Range	Formula
D2	=VLOOKUP(E2,B:C,2,FALSE)
D3	=VLOOKUP(E3,B:C,2,FALSE)
D4	=VLOOKUP(E4,B:C,2,FALSE)
D5	=VLOOKUP(E5,B:C,2,FALSE)
D6	=VLOOKUP(E6,B:C,2,FALSE)
D7	=VLOOKUP(E7,B:C,2,FALSE)
D8	=VLOOKUP(E8,B:C,2,FALSE)
D9	=VLOOKUP(E9,B:C,2,FALSE)
D10	=VLOOKUP(E10,B:C,2,FALSE)
D11	=VLOOKUP(E11,B:C,2,FALSE)
D12	=VLOOKUP(E12,B:C,2,FALSE)
D13	=VLOOKUP(E13,B:C,2,FALSE)
D14	=VLOOKUP(E14,B:C,2,FALSE)
D15	=VLOOKUP(E15,B:C,2,FALSE)
D16	=VLOOKUP(E16,B:C,2,FALSE)
D17	=VLOOKUP(E17,B:C,2,FALSE)
D18	=VLOOKUP(E18,B:C,2,FALSE)
D19	=VLOOKUP(E19,B:C,2,FALSE)
D20	=VLOOKUP(E20,B:C,2,FALSE)
D21	=VLOOKUP(E21,B:C,2,FALSE)
D22	=VLOOKUP(E22,B:C,2,FALSE)
D23	=VLOOKUP(E23,B:C,2,FALSE)
D24	=VLOOKUP(E24,B:C,2,FALSE)
D25	=VLOOKUP(E25,B:C,2,FALSE)
D26	=VLOOKUP(E26,B:C,2,FALSE)
D27	=VLOOKUP(E27,B:C,2,FALSE)
D28	=VLOOKUP(E28,B:C,2,FALSE)
D29	=VLOOKUP(E29,B:C,2,FALSE)
D30	=VLOOKUP(E30,B:C,2,FALSE)
D31	=VLOOKUP(E31,B:C,2,FALSE)
D32	=VLOOKUP(E32,B:C,2,FALSE)
D33	=VLOOKUP(E33,B:C,2,FALSE)
D34	=VLOOKUP(E34,B:C,2,FALSE)
D35	=VLOOKUP(E35,B:C,2,FALSE)
D36	=VLOOKUP(E36,B:C,2,FALSE)
D37	=VLOOKUP(E37,B:C,2,FALSE)
D38	=VLOOKUP(E38,B:C,2,FALSE)
D39	=VLOOKUP(E39,B:C,2,FALSE)
D40	=VLOOKUP(E40,B:C,2,FALSE)
D41	=VLOOKUP(E41,B:C,2,FALSE)
D42	=VLOOKUP(E42,B:C,2,FALSE)

I need to find unique (only one) values in Column D:E. The numbers are combinations. I need to find the values that have only one item number in the column.

Thank you in advance for any and all assistance.

 

[gaz_chops]

Try

=IF(COUNTIF($B$2:$B$200,B2)=1,"Unique","")

 

[hiker95]

SOLTEC,

You did not say where you wanted the results to be written to.

I do not have your actual raw data worksheet, so I put simple formulae in column D to represent the numbers.

Raw data, and, results in the same screenshot:

Excel 2007
	D	E	F	G
1		Product Name		Product Name
2	1	Replacement Boot Laces	1	Replacement Boot Laces
3	1	Replacement Boot Laces	3	Blousing Garters
4	3	Blousing Garters	7	GI Extreme Cold Weather Bunny Boots
5	3	Blousing Garters	13	Nylon Commando Wallet
6	3	Blousing Garters	20	Pepper Sprays Gel Sabre Red
7	3	Blousing Garters	21	Fire Master Fog Pepper Spray
8	7	GI Extreme Cold Weather Bunny Boots	26	Key Chain Pepper Spray
9	7	GI Extreme Cold Weather Bunny Boots	28	Cop Top Fog Cone Pepper Spray
10	7	GI Extreme Cold Weather Bunny Boots	29	Flip Top Stream Pepper Spray
11	7	GI Extreme Cold Weather Bunny Boots	30	Flip Top Cop Med Fog Cone Pepper Spray
12	7	GI Extreme Cold Weather Bunny Boots	32	Cop Top Stream Pepper Spray
13	7	GI Extreme Cold Weather Bunny Boots	40	Police Magnum Sabre Red
14	13	Nylon Commando Wallet
15	13	Nylon Commando Wallet
16	13	Nylon Commando Wallet
17	13	Nylon Commando Wallet
18	13	Nylon Commando Wallet
19	13	Nylon Commando Wallet
20	13	Nylon Commando Wallet
21	20	Pepper Sprays Gel Sabre Red
22	21	Fire Master Fog Pepper Spray
23	20	Pepper Sprays Gel Sabre Red
24	20	Pepper Sprays Gel Sabre Red
25	20	Pepper Sprays Gel Sabre Red
26	20	Pepper Sprays Gel Sabre Red
27	26	Key Chain Pepper Spray
28	20	Pepper Sprays Gel Sabre Red
29	28	Cop Top Fog Cone Pepper Spray
30	29	Flip Top Stream Pepper Spray
31	30	Flip Top Cop Med Fog Cone Pepper Spray
32	20	Pepper Sprays Gel Sabre Red
33	32	Cop Top Stream Pepper Spray
34	20	Pepper Sprays Gel Sabre Red
35	28	Cop Top Fog Cone Pepper Spray
36	29	Flip Top Stream Pepper Spray
37	30	Flip Top Cop Med Fog Cone Pepper Spray
38	32	Cop Top Stream Pepper Spray
39	20	Pepper Sprays Gel Sabre Red
40	20	Pepper Sprays Gel Sabre Red
41	40	Police Magnum Sabre Red
42	20	Pepper Sprays Gel Sabre Red
43
Workspace

Cell Formulas
Range	Formula
D2	=1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniques()
' hiker95, 03/11/2015, ME841664
With Sheets("Workspace")
  [D1] = "D"
  .Range("D1:E" & .Cells(Rows.Count, 5).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("F:G"), Unique:=True
  [F1] = ""
  [D1] = ""
  .Columns("F:G").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the GetUniques macro.