Hello guys,
I've tried to search answer for this tough nut for quite some time already, without a success.
What should be the formula on Column B in the following case:
A1 value = "22-25-29---/29-----"
B1 should return 29
A2 value = "45-109----/28-45-108---"
B1 should return 45
A3 value = "33-46-24-56-/12-23-56-60-"
B3 should return 56
and so on....
I bet this should be done with some kind of array formula or alternatively with VBA.
Thanks in advance for anyone who could help.
Probably should be a VBA solution....but, I saw a formula challenge and had to accept
With these values in A1:A3
Each of these solutions (copied down) returns the duplicate value.
Please Login or Register to view this content.
2 helper-cell solution:
Please Login or Register to view this content.
1 helper-cell solution:
Single Cell solution:Please Login or Register to view this content.
The returned values are:Please Login or Register to view this content.
29
45
56
Is that something you can work with?
Also, array formula**:
=MATCH(TRUE,(LEN("-"&SUBSTITUTE(A1,"/","-"))-LEN(SUBSTITUTE("-"&SUBSTITUTE(A1,"/","-"),"-"&ROW(INDIRECT("1:999"))&"-","")))/(2+LEN(ROW(INDIRECT("1:999"))))>1,0)
which will work for values up to and including 999. Change this value if required.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Hi!
So you have:
A1 value = "22-25-29---/29-----"
B1 should return 29
A2 value = "45-109----/28-45-108---"
B1 should return 45
A3 value = "33-46-24-56-/12-23-56-60-"
B3 should return 56
What would you like to see from A4 if it contains the following?
A4 value = "33-46-23-56-/12-23-56-60-"
Would you want B4 to return 23, or 56? Or both?
1) Concise formula....for small duplicate values.
2) Those darned volatile INDIRECT functions are sluggish for values up to 9999...and they're a show stopper for values up to 999999
(I tested it on 1000 rows of test values and I had to shut down Excel when it took 5 minutes for the calc meter to increment up to 4%!)
3) The approach I took, while admittedly inelegant (actually it's as ugly as a mud fence), is surprisingly instantaneous.
4) I don't envy whoever would need to use either approach!
True. I took a hunch based on the sample data that maybe 3 digits was the maximum string length possible, though if not then of course my solution will certainly become rather resource-heavy!Originally Posted by Ron Coderre
Regards
I was trying to solve this one over an hour earlier today and just couldn't get it together without having 10 helper cells which wouldn't be practical for my purpose.
It works like a charm
If you are interested, I need to match filenames with corresponding employees and with your formula I will be able to identify when both Surname and First name have matched on the same row.
I have only 100 something names in the list so there is very unlikely chanse that both surname and name would be same for two individuals ( Values on left side and right side of the "/" are row matches of names and surnames).
For example following:
Names:
John Smith
Arnold Smith
Jessica Pearsson
Files:
AC_John_Smith_file908.xlsx
SA_results_Pearsson_Jessica.xlsx
Arnold_Smith_survey_results.xlsx
Thank you one more time. I didn't believe it would be even possible without an Array formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks