Find the latest price and lowest price out of formatted text
Hi,
I have attached the excel workbook where sheet named "Before" contains some prices in column A. I want to find out the latest price and lowest price out of those excel cells and the desired output is shown in sheet named "After". You can ignore the term "sold as special offer" as described in sheet named "Note".
When you will open the excel file then you will understand the whole problem in one glance. Many thanks for all the contributors.
Best Regards,
In B2 this will return the latest price.Formula:
Please Login or Register to view this content.
The other is going to take me a while.
Are you open to helper columns?
Dave
Hi,
Thanks for the first solution.
I don't understand "helper columns".
I am just interested in the solution. You can solve this problem the way you want.
They are additional formulas which break down and simplify the final solution.Originally Posted by anonymous321
Its okay. You can go with most reliable and simple solution.
This proposed solution uses A LOT of helper columns.
The first one (D) is populated using:Formula:Please Login or Register to view this content.
Column E is populated using:Formula:Please Login or Register to view this content.
Columns H:CL are populated using*:Formula:Please Login or Register to view this content.
Column C is populated using*:Formula:Please Login or Register to view this content.
Note: Column F is populated with a formula which I hope can be incorporated into a user defined function (unfortunately I am VBA illiterate, but I'll ask some members from the VBA forum to take a look). If a UDF solution is possible then it should eliminate the need for a least the formula in columns H:CL.
Note: I am returning this as an .xlsx file because your profile indicates that you are using the 2007 version.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Your sample file says the latest price should be the first price from the right, but the examples are all the first from the left, so which is correct?
Don
Please remember to mark your thread 'Solved' when appropriate.
I have the same question as Don. I settled on left most figures. Additionally I get AU $55.30 for minimum price in A4. The anticipated is AU $59.95.
In the attached column D find this for the latest figure. It's the same one I posted in post #2 and does double duty as a helper in the min formula.Formula:Please Login or Register to view this content.
Then for the minimums two helper formulas ... columns E:F ...
This cleans up and "standardizes" the strings.This one locates the last "." in the standardized helpers.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Then array enter this in G2 and filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Try this, it's a start:
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks