Hi All,

My problem is as follows:
In my spreadsheet I have records as follows

Customer number| title | First | Last
1234 Mr John Doe
1234 Mrs Jane Doe

So they are different people at the same address (obviousl married) who
share the same "Unique" customer number. I need to exctract all of these from
my datalist into a new spreadsheet. I have tried an "If" statement but that
will only flag the first record out of the matching pair. Any suggestions??

Thanks!

Cn= Customer number

In Sheet 2
B2:
=IF(ISERR(SMALL(IF(Cn=$A$2,ROW(INDIRECT("1:"&ROWS( Cn)))),ROWS($1:1))),"",INDEX(Title,SMALL(IF(Cn=$A$ 2,ROW(INDIRECT("1:"&ROWS(Cn)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy across to D2, in C2 change "Title" with "First", in D2 change "Title"
with "Last".
Select B2:D2 and copy down as far as needed.


"davids" wrote:

Hi All,

My problem is as follows:
In my spreadsheet I have records as follows

Customer number| title | First | Last
1234 Mr John Doe
1234 Mrs Jane Doe

So they are different people at the same address (obviousl married) who
share the same "Unique" customer number. I need to exctract all of these from
my datalist into a new spreadsheet. I have tried an "If" statement but that
will only flag the first record out of the matching pair. Any suggestions??

Thanks!

datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"davids" wrote in message
...
Hi All,

My problem is as follows:
In my spreadsheet I have records as follows

Customer number| title | First | Last
1234 Mr John Doe
1234 Mrs Jane Doe

So they are different people at the same address (obviousl married) who
share the same "Unique" customer number. I need to exctract all of these
from
my datalist into a new spreadsheet. I have tried an "If" statement but
that
will only flag the first record out of the matching pair. Any
suggestions??

Thanks!

No that function doesnt appear to do anything :/
I was thinking of something along the lines of creating a new column next to
customer number and saying If CustNo A1 = CustNo A2 mark as True else False
but that will only put true next to one of the records where I would need it
next to both so I could delete all instances of "False", sort the list, cut
and paste all instances of "True" into another worksheet

"davids" wrote:

Hi All,

My problem is as follows:
In my spreadsheet I have records as follows

Customer number| title | First | Last
1234 Mr John Doe
1234 Mrs Jane Doe

So they are different people at the same address (obviousl married) who
share the same "Unique" customer number. I need to exctract all of these from
my datalist into a new spreadsheet. I have tried an "If" statement but that
will only flag the first record out of the matching pair. Any suggestions??

Thanks!

Sort the data by customer number, insert a new column B and put this
in B2:

=IF(OR(A2=A1,A2=A3),"matched pair","single")

Copy down, then apply autofilter to column B and choose "matched pair"
from the drop-down. Highlight all the visible cells, click <copy then
paste into a new worksheet.

Hope this helps.

Pete


On Oct 8, 4:30 pm, davids wrote:
No that function doesnt appear to do anything :/
I was thinking of something along the lines of creating a new column next to
customer number and saying If CustNo A1 = CustNo A2 mark as True else False
but that will only put true next to one of the records where I would need it
next to both so I could delete all instances of "False", sort the list, cut
and paste all instances of "True" into another worksheet



"davids" wrote:
Hi All,

My problem is as follows:
In my spreadsheet I have records as follows

Customer number| title | First | Last
1234 Mr John Doe
1234 Mrs Jane Doe

So they are different people at the same address (obviousl married) who
share the same "Unique" customer number. I need to exctract all of these from
my datalist into a new spreadsheet. I have tried an "If" statement but that
will only flag the first record out of the matching pair. Any suggestions??

Thanks!- Hide quoted text -

- Show quoted text -