Data Sorting Query
 

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!

Data Sorting Query
 

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!

Data Sorting Query
 

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!

Data Sorting Query
 

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!

Data Sorting Query
 

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 -