Finding duplicate results, returning different values

[EdwardTLC]

So have been trying to figure out how to utilize VLOOKUP or some other formula that will allow me to search a column for a duplicate value and return a different column value than the original value. Confusing?

Specifically in the chart pic I have uploaded, cell B2 would have a formula that would take the value in C2, find it as a duplicate in Column C and return the value in column A.

In this instance, it would find the duplicate in C12 and return the value in A12 put it in cell B2.

Thank you for any help in advance

Ed

 

[Eric W]

Welcome to the MrExcel forum!

FYI, please try to use the XL2BB tool to show your sample worksheet. Look for the button in the reply box. It makes it so much easier for somebody here to copy and work with your sheet, rather than having to type in your data from a picture. I use it below. That could explain why you haven't had any replies yet.

That said, try this:

Book1
	A	B	C	D
1	Loc1	Loc2	Part #	Description
2	S1A	S2B	24365	Part A
3	S1A		26	Part B
4	S1A	S2B	2456	Part C
5	S1B		345	Part D
6	S1B	S3A	45657	Part E
7	S1B		92187	Part F
8	S2A	S3B	356	Part G
9	S2A		7645	Part H
10	S2A	S3C	234	Part I
11	S2B		546	Part J
12	S2B	S1A	24365	Part A
13	S2B	S1A	2456	Part C
14	S3A	S1B	45657	Part E
15	S3B	S2A	356	Part G
16	S3C	S2A	234	Part I
17
Sheet15

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/($C$2:$C$20=C2)/(ROW(C2)<>ROW($C$2:$C$20)),1)),"")

 

[EdwardTLC]

Thank you so much. I didn't know about the functions here as I just joined. I am still having a little issue.

My spreadsheet is around 3000 lines so I thought I could just adapt, by changing all the formula values. I have attached a sample this time of the chart so you can see the breaks ad the challenges I am having. One of the things I didn't consider in the initial question was many of the part numbers are Alpha Numeric. Not sure if this makes a difference when using the 'Aggregate' function. I tried using just numeric part numbers and I still couldn't make the formula work so I thought I would upload a large, but small portion of the original doc below.

Inventory Location.xlsx
	A	B	C	D
1	Company Inventory
2
3	Loc1	Loc2	Part #	Description
4	S1A		K916514	Hot Dipped Steel Hinge
5	S1A		KP000828	Swing Door Hinge Assy
6	S1A		K199993	3.5" Alum Hinge Strap
7	S1A		K9461591	Chain Snap Holdback
8	S1A		K916649	Footman Loop, Door Holder
9	S1A		K940725	Spring Loaded Safety Hook
10	S1A		K944282	SS Hinge Butt
11	S1A		K916155	Hinge Bushing
12	S1A		K911473	SSteel Hinge Pin
13	S1A		K944336	SS Nylock Nut
14	S1A		K18067385	3-1/2" Door Bumper Retainer
15	S1A		K916427	White Plastic ABS Recessed Box
16	S1A		K916139	Rubber Extrusion U-253 x 3-1/2"
17	S1A		K947565	Folding Footstep/Handheld
18	S1A		GR822190	15 Amp Circuit Breaker w/Bracket
19	S1A		2131098	Bulkhead Handle Assy
20	S1A		3060111	End Stop, 16RT
21	S1A		3130585	BRACKET SHIM, 3/16"
22	S1A		3131133	Lift Block
23	S1A		3131028	Plastic Pipe Clamp Shim
24	S1A		3130409	Log Strap Washer/Plate
25	S1A		GR44840	30amp, 3-Prong Relay
26	S1A		AS1250	12V 5-PRONG RELAY W/O DIODE
27	S1A		GR60381	SuperNova LED License Light
28	S1A		1XC27	60 Minute Spring Wound Timer
29	S1A		44767390	Timer Ivory Knob Kit
30	S1A		GS100	Wall Plate/Gang Box Gasket
31	S1A		1LXW9	Ivory Gang Box/Wall Plate
32	S1A		41700301	Dock Bumper, 2 Hole, 3x6
33	S1A		454347	Galvanized Wire Kit, W/Clamps
34	S1A		5131023	2pc Log Strap Conn w/hardware
35	S1A		2131113	Connector Strap W/D-Ring
36	S1A		2131103	2" D-Ring Assy, Lift System
37	S1A		2130037	Log Strap Assy E, 4'6"
38	S1A		6500598	Hubodometer
39	S1A		6500608	Hubodometer
40	S1A		K9461591	Chain Snap Holdback
41
42
43	Loc1	Loc2	Part #	Description
44	S1A		1LXW9	Ivory Gang Box/Wall Plate
45	S1A		6100028	4.5" Hubometer Bracket
46	S1A		S4461082010	Meritor ABS ECU
47	S1A		S24691	QuikAlign Pvt Bolt Kit
48	S1A		90030880	Cab Air Spring
49	S1A		1S5006	Cab Suspension Air Bag
50	S1A		32966601	Cab Seat Air Bag
51	S1A		S227	Height Control/Leveling Valve
52	S1A		1277901	H8200 SLIDER PIN SPRING
53	S1A		3060006	Side Wall Track Bracket
54	S1A		PTL8001	Rear LED Box Lamp W/Window
55	S1A		SP70210	7" Round Recess Pan
56	S1A		SP80210	4" Recess Pan
57
58
59	Loc1	Loc2	Part #	Description
60	S1B		GR44240	Electronic Dome Lamp Switch
61	S1B		K918124	104" ABS Female Soft Door Seal
62	S1B		K918123	104" ABS Male Soft Door Seal
63	S1B		GDP45985	D-Style Door Seal, P/Ft
64	S1B		TOD69718	Panel Mounted Head Seal
65	S1B		TOD70131	Side Seal, Dual Lip
66	S1B		TRUESEAL	Trueseal Door Seal
67	S1B		WHI7470	Whiting Door Side Seal
68	S1B		43004	Rear Door Refrig Bulb Seal
69	S1B		K916141	Door Rubber, U-89
70	S1B		2131098	Bulkhead Handle Assy
71	S1B		V21680	Inner Door Seal
72	S1B		V1081680	Inner Door Seal
73	S1B		K944282	SS Hinge Butt
74	S1B		33550038	Dual Durometer Door Gasket
75	S1B		NEC6346701	CORNER SEAL, P/FT
76	S1B		NEC6927771	14' OUTER DOOR TRIM
77
78
79	Loc1	Loc2	Part #	Description
80	S1C		9380SWD	Universal Dome Lamp w/switch
81	S1C		GR55340	2 Stud Red/Amber Light
82	S1C		GR64931	Par 36 Rubber Tractor and Utility Light
83	S1C		PTL8001	Rear LED Box Lamp W/Window
84	S1C		GR53292	SuperNova® LED S/T/T Light
85	S1C		TL44322R	6-LED S/T/T Light w/Grey Flange
86	S1C		TL41202R	Square Sealed S/T/T Light, LH
87	S1C		STL75AB	Amber Mid Turn LED Light
88	S1C		DOTM	R/S DOT Tape, 18", P/PC
89	S1C		MYLAR	Mylar Tape, Roll
90	S1C		DSTAPE	Double Sided Mounting Tape
91	S1C		15PTAPE	1.5" Blk Foam Tape, 50'
92	S1C		RTAPE	Roof Bow Tape, Roll
93	S1C		1PTAPE	1" Panel Tape, Roll
94	S1C		8X300S	300' Smooth Clr Vinyl Strip
95	S1C		12X150R	150' Ribbed Clear Vinyl Strip
96	S1C		14PTAPE	1/4" Panel Tape, 150'
97
98
99	Loc1	Loc2	Part #	Description
100	S1D		TL41	3" Red Refl W/Alum Mount
101	S1D		TL41A	3" Amber Refl W/Alum Mount
102	S1D		TL52	2" Red Refl W/Hole Mount
103	S1D		TL52A	2" Amber Refl W/Hole Mount
104	S1D		TL54	4X2 Red Oblong Refl W/Adhesive
105	S1D		TL54A	4X2 Amber Oblong Refl W/Adhesive
106	S1D		TL98003Y	4X1 Amber Refl W/Adhesive
107	S1D		TL98003R	4X1 Red Refl W/Adhesive
108	S1D		TL98006Y	3" Amber Refl W/Center Mount
109	S1D		TL98006R	3" Red Refl W/Center Mount
110	S1D		TL21051R	Red LED C/M Light w/Reflex Lens
111	S1D		TL44302R	44-Led, 4" S/T/T Light
112	S1D		TL10202Y	Amber Selaed 2.5" C/M Light
113	S1D		TL10202R	Red Selaed 2.5" C/M Light
114	S1D		GR1033	Amber 2.5" LED C/M Light
115	S1D		GR1032	Red 2.5" LED C/M Light
116	S1D		PT3000A	Amber 2" Sealed Incand Light
117	S1D		PT3000R	Red 2" Sealed Incand Light
118	S1D		TL30257Y	2" Round Amber 'ABS' Light
119	S1D		TL10414	2.5" Light Bracket Kit
120	S1D		TL30404	Plastic Mount W/Grom for 2" Lite
121	S1D		TL10202C	Clear Selaed 2.5" C/M Light
122	S1D		TL33204R	Red 3/4" 1 LED Light, W/Grmt
123	S1D		TL33250Y	AMBER 3/4" LED RND C/M LIGHT
124	S1D		TL33250R	RED 3/4" LED RND C/M LIGHT
125	S1D		GR53102	Red 4" Incand S/T/T Sealed Light
126	S1D		TL60216Y	6" Oval Amber Turn W/Flange
127	S1D		PT2601A	Amber Oval Incand C/M Light
128	S1D		PT2601ALN	Amber Oval Lens Cover
129	S1D		PT2601R	Red Oval Incand C/M Light
130	S1D		PT2601RLN	Red Oval Lens Cover
131	S1D		PT2602A	Amber Double Bullseye C/M Light
132	S1D		PT2602ALN	Amber Bullseye Lens
133	S1D		PT2602R	Red Double Bullseye C/M Light
134	S1D		PT2602RLN	Red Bullseye Lens
135	S1D		GR92163	2X6 Amber Reflex Repl Lens
136	S1D		GR92162	2X6 Red Reflex Repl Lens
137	S1D		GR91043	Red Lens Cover, Reflexive
138	S1D		GR91042	Red 2-Bulb Reflex Replacement Lens
139	S1D		GR90172	Red Rect S/T/T Repl Lens
140
141
142	Loc1	Loc2	Part #	Description
143	STOR		GR90173	Amber Rect S/T/T Repl Lens
144	STOR		TL15200R	2" RED INCAD LIGHT
145	STOR		TL15200Y	2" AMBER INCAD LIGHT
146	STOR		TL15200C	2" WHITE INCAD TAG LIGHT
147	STOR		TL15414	Rail Mounting Bracket/Plug
148	STOR		TL15412	Rail Mounting Bracket w/cutout
149	STOR		PT2602ALN	Amber Bullseye Lens
150	STOR		TL19031R	Red LED Marker Light/Mnt Kit
151	STOR		K944282	SS Hinge Butt
152	STOR		TL19200R	Red Incand C/M Light
153	STOR		TL19250Y	Amber 4" LED C/M Light
154	STOR		TL19250R	Red 4" LED C/M Light
155	STOR		TL19350Y	Amber LED C/M Light
156	STOR		TL19350R	Red LED C/M Light
157	STOR		A78CB1	6" Plastic Oval Chrome Ring
158	STOR		TL19748	Chrome Base Mount
159	STOR		TL19200C	4" WHITE SEALED UTILITY LIGHT
160	STOR		TL19720	Grey LED Light Mount
161	STOR		TL10202R	Red Selaed 2.5" C/M Light
162	STOR		TL19721	Flat Base Mount, Grey
163	STOR		TL19738	Curved Base Mount, Grey
164	STOR		GR52063	Aero Comb Marker/Side Turn Light
165	STOR		GR54002	Red SNova® TB II LED S/T/T Light
166	STOR		TL21251Y	AMBR LED C/M LIGHT W/REFLEX LENS
167	STOR		TL21251R	RED LED C/M LIGHT W/REFLEX LENS
168	STOR		TL21002R	Red Rectangular Sealed Light
169	STOR		TL21002Y	Amber Rectangular Sealed Light
170	STOR		GR47073	Amber 2" x 6" LED C/M Light
171	STOR		GR47072	Red 2" x 6" LED C/M Light
172	STOR		GR46803	Amber Incand Turtle II C/M Light
173	STOR		GR46802	Red Incand Turtle II C/M Light
174	STOR		GR47163	Amber SuperNova® LED Light
175	STOR		GR47162	Red SuperNova® LED Light
176	STOR		GR43253	SS Theft Resistant 4" Flange
177	STOR		GR53252	4″ 10-Dio Red LED S/T/T Lamp
178	STOR		GR47962	Red MicroNova® LED C/M Light
179	STOR		GR54233	SuperNova® LED Side Turn Signal
180	STOR		GR47963	Amber MicroNova® LED C/M Light
181	STOR		GR52533	Amber 6" Oval Turn/Marker Lamp
182	STOR		TL60275Y	LED Oval Turn Sig/Marker Lamp
Sheet1

 

[Eric W]

Thank you so much. I didn't know about the functions here as I just joined. I am still having a little issue.

No worries! We're all new sometimes. Kudos on figuring it out so quickly. Here's how you adapt that formula to the range you posted:

Book1
	A	B	C	D
1	Company Inventory
2
3	Loc1	Loc2	Part #	Description
4	S1A		K916514	Hot Dipped Steel Hinge
5	S1A		KP000828	Swing Door Hinge Assy
6	S1A		K199993	3.5" Alum Hinge Strap
7	S1A	S1A	K9461591	Chain Snap Holdback
8	S1A		K916649	Footman Loop, Door Holder
9	S1A		K940725	Spring Loaded Safety Hook
10	S1A	S1B	K944282	SS Hinge Butt
11	S1A		K916155	Hinge Bushing
12	S1A		K911473	SSteel Hinge Pin
13	S1A		K944336	SS Nylock Nut
14	S1A		K18067385	3-1/2" Door Bumper Retainer
15	S1A		K916427	White Plastic ABS Recessed Box
16	S1A		K916139	Rubber Extrusion U-253 x 3-1/2"
17	S1A		K947565	Folding Footstep/Handheld
18	S1A		GR822190	15 Amp Circuit Breaker w/Bracket
19	S1A	S1B	2131098	Bulkhead Handle Assy
20	S1A		3060111	End Stop, 16RT
Sheet16

Cell Formulas
Range	Formula
B4:B20	B4	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$4:$A$200)/($C$4:$C$200=C4)/(ROW(C4)<>ROW($C$4:$C$200)),1)),"")

I didn't want to post the whole range again, there's a few in this sample that shows it working. It shouldn't matter if the values are numeric or alphanumeric, as long as they are the same.

 

[EdwardTLC]

I figured out why it wasn't working the first time. I was using Google Sheets rather than Excel. (I have it saved both ways) Google sheets does not recognize the Aggregate formula.

Thank you so much for your help, you saved me having to Sort and resort values.

Ed

 

[Eric W]

It's a good idea to mention you're using Google Sheets. While Google Sheets and Excel are similar, there are significant differences, as you've seen. This formula might work in Google Sheets:

=ARRAYFORMULA(IFERROR(INDEX(A:A,SMALL(IF(($C$4:$C$200=C4)*(ROW(C4)<>ROW($C$4:$C$200)),ROW($A$4:$A$200)),1)),""))

but I have no way to test it.

I'm glad you got the Excel version working.