Compare 2 lists of random numbers - Excel 2007

[Wanda]

Thanks in advance for any and all help!!!

I have Excel 2007. I have a file of that contains 3 sheets of similar
information - account number, balance, name, due date, high credit, etc.
This is a non-logical segmentation of my company's entire card base (I work
for a small card processor). Unfortunately, in converting the original
information prior to segmenting accounts, it was not discovered that Excel
only converted 15 of the 16 digits of the account number, changing the last
number to zero. I was able to re-import my original information - importing
my account number as text instead of numbers, so that now I have a file with
the correct account numbers, but they're not segmented in to my non-logical
groups. I tried doing "Remove Duplicates" - by comparing the names, but that
didn't do anything. I can't do "remove Duplicates" by balances, since there
are a great many cards that have a zero balance, and I can't match "John
Doe's zero balance card" to "Jane Smith's zero balance card".

Short of comparing all 9500 lines of the file and manually segmenting the
cards again, do you awesome gurus have any suggestions?

My incorrect, segmented file has incorrect account numbers, like
1234-5678-1234-5670, when the correct non-segmented file has the correct
number like 1234-5678-1234-5679

 

[Sean Timmons]

Perhaps using =LEFT(A2,15) so you can compare the first 15 to each. If you do
that and use a countif(), you can determine how many rows have the same first
15 characters...

 

[ryguy7272]

That's what I'd do, Sean. If this is the setup:
Column A (should be in A1):
1234-5678-1234-5670

Column B (anywhere in the column):
1234-5678-1234-5679

Column C (should be in C1)
Jack Black

Put this function in some column, like F:
=IF(LEFT(COUNTIF(A1:A20,B1:B20)>0,15)=LEFT(COUNTIF(A1:A20,B1:B20)>0,15),C1)

Anyway, try it and see what you get.


HTH,
Ryan--

 

[ryguy7272]

That's what I'd do, Sean. If this is the setup:
Column A (should be in A1):
1234-5678-1234-5670

Column B (anywhere in the column):
1234-5678-1234-5679

Column C (should be in C1)
Jack Black

Put this function in some column, like F:
=IF(LEFT(COUNTIF(A1:A20,B1:B20)>0,15)=LEFT(COUNTIF(A1:A20,B1:B20)>0,15),C1)

Anyway, try it and see what you get.


HTH,
Ryan--

 

[Wanda]

Thanks Ryan and Sean! Actually, I was able to talk to the person that
divvy'd up the original incorrect form, and he sent me off in a different
direction that worked. What I did was re-import the text file to excel 2
times - once with the incorrect "truncated" account numbers, and again with
the correct "text" numbers. Then I inserted a column in the "correct" sheet,
and copied the "truncated" numbers in to it. From there it was pretty easy
to match everything up.

Thanks again!!!!!