Duplicates matching other columns

Boycee

Board Regular
Joined
Nov 23, 2002
Messages
109
Would it be possible to do this.

I have a spreadsheet with 4 columns
Column
A – Postcode
B – Reference Number
C – Name
D – G or H not both (these letters either G or H are not in every cell in column D)

What I would like is to find the following:
only rows where
1 There is a duplicated Postcode entry in column A and
2 At least one of these duplicate postcode entries has a reference number in column B and
3 At least one of these duplicate postcode entries has the letter G or H in column D

So I need any duplicate postcodes where there is a reference number and an entry in column D but it doesn’t have to be the same postcode.

e.g 5 duplicate postcodes found in column A, one of them has a reference in column B and one has an enrty in column D.

Hope someone can help me.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
<<So I need any duplicate postcodes where there is a reference number and an entry in column D but it doesn’t have to be the same postcode. >>

The above sentence does not "compute".


A 60 second solution (if you are slow) :-

Make a pivot table of your data using Columns A, C, and D as ROW data and Count of PostCode as DATA.

As far as I can see results obtained by sorting on postcode and using worksheet formulas would not be better.
 
Upvote 0
The systemstripped out my quote. The sentence in question is :-

'----------------------------------------------------------------------------
So I need any duplicate postcodes where there is a reference number and an entry in column D but it doesn’t have to be the same postcode.
'---------------------------------------------------------------------------------
 
Upvote 0
Hi,

If I understood you correctly, I think this will do.

Long formulas though so if you could use a pivot that would be better.

Formula in E column is an array formula and must be entered with Ctrl + Shift + Enter.
Book2
ABCDEFGH
1coderefnameG/HMatching
21111aa111111
31111xbbg113333
42222cc01 
52222dd01 
63333xee12 
73333ffh12 
84444xgg02 
94444xhh02 
105555xiig02 
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top