Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I am sorting an old spreadsheet with lots of expired contracts. Column X shows all the product serial numbers. Column A shows the contract expiration date relating to the serial number. The serial numbers are often duplicated where a customer has renewed their cover for the same product.over several years. I want to create a new column which sorts out those expired contracts between "Expired - Renewed" and "Expired - didn't Renew". Help! Please!
Solved by B. J. in 53 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/04/2018 - 05:03
hello there! :)
User 13/04/2018 - 05:03
hi there
Excelchat Expert 13/04/2018 - 05:04
Can you upload your working file so we could do a little matching of column A and X? Thanks! :)
User 13/04/2018 - 05:06
User 13/04/2018 - 05:07
highlighted in red are duplicates
Excelchat Expert 13/04/2018 - 05:07
I see. So if the serial no. was renewed even just once, it should enter in the status as "Expired - Renewed" right?
User 13/04/2018 - 05:08
Yes please - "Expired - Renewed" or "Expired - Did not Renew"
Excelchat Expert 13/04/2018 - 05:08
Okay, let me work on it for a bit then. Thanks for the clarification! :)
User 13/04/2018 - 05:08
You're welcome!
User 13/04/2018 - 05:09
Excelchat Expert 13/04/2018 - 05:13
Excelchat Expert 13/04/2018 - 05:17
Just did the formula in column D :)
Excelchat Expert 13/04/2018 - 05:17
You may as well see attached for the sample document. :)
[Uploaded an Excel file]
User 13/04/2018 - 05:17
But it doesn't match what the status should return ...??? !
Excelchat Expert 13/04/2018 - 05:18
Ohh I see. Okay let me redo that part. :)
User 13/04/2018 - 05:18
See ESA7902503...they took out a contract which expired in 2007 but then they took out a contract again and renewed their contract. However by 2008 they decided not to renew! ;-) no worries!!
Excelchat Expert 13/04/2018 - 05:19
Yeah, I just realized that right now. Let me make the fix. :)
User 13/04/2018 - 05:19
User 13/04/2018 - 05:21
I'm in awe if you can make this work.
Excelchat Expert 13/04/2018 - 05:24
Lol, this may be challenging, but I'll try my best. I think there's a way around this. :)
User 13/04/2018 - 05:24
I've been trying for over an hour LOL
Excelchat Expert 13/04/2018 - 05:47
Lol! the dates are formatted as "dd/mm/yyyy" right? I had a formula minutes ago and I was wondering why it's not working.
User 13/04/2018 - 05:47
not sure - reset it on this to that format just to be sure
Excelchat Expert 13/04/2018 - 05:48
B4 for example is formatted as dd/mm/yyyy. that's why it's not picking up right.
Excelchat Expert 13/04/2018 - 05:48
coz the Excel is reading B2 for example as mm/dd/yyyy. But it may just be dd/mm/yyyy all along?
User 13/04/2018 - 05:49
the dates should be UK format - dd/mm/yyyy
User 13/04/2018 - 05:51
can't work out how to check the date format in your example but I've modified my main spreadsheet to ensure it's dd/mm/yyyy
User 13/04/2018 - 05:52
I can't seem to right click and format cells on the example to the right?!
Excelchat Expert 13/04/2018 - 05:53
Done! I modified the date samples as "dd/mm/yyyy" and the formula went well. :)
[Uploaded an Excel file]
User 13/04/2018 - 05:54
YAY thank you so much :)
User 13/04/2018 - 05:54
Very clever!
Excelchat Expert 13/04/2018 - 05:54
Sure thing! Thanks as well. Sorry it took so long, it was the date format after all. I was wondering why it went well for other cases but not on some.
User 13/04/2018 - 05:54
So the next time I want to utilise your services, how much is it please?
User 13/04/2018 - 05:55
No worries - it was complicated (well to me anyway!)
Excelchat Expert 13/04/2018 - 05:56
Not really sure how much lol. You can always ask here though. :) Anyhow, Glad we worked it out. You may end the session if all is well (can't do it on my side). Please leave a good feedback btw. Thanks again and have a nice day! :)
User 13/04/2018 - 05:56
I must tell my partner Neil about this service - can you send me an email so I can retain your details.
User 13/04/2018 - 05:56
Where do I give feedback/
User 13/04/2018 - 05:56
Thanks again and have a great day
Excelchat Expert 13/04/2018 - 05:57
You may give feedback once you end the session. You may always utilize this platform anytime! As per policy though, customers and experts should remain confidential. :)
User 13/04/2018 - 05:57
Great thank you!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc