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
ok?
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
Hi!
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
haha
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.
