Data Sorting Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Back
Top