LinkBack URL
About LinkBacks
How can I compare different parts of strings to similar parts in a different cell?
How can I compare different parts of strings to similar parts in a different cell?
You can do this many ways, but how about providing some examples.
HTH
Regards, Jeff
You can make a good start by reading the yellow banner (Top of page) and attaching a sample sheet, showing raw data and expected output (manually calculated).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Unfortunately I can't post links yet because i only joined today
however, one cell would contain the following as an example
1 14GA WHT
or
2 12GA RED
What are you comparing? Is see the examples, but what do they mean to us? Even with a low post count, you can still attach a sample worksheet.
You CAN post a file, you CAN NOT post links. Re-read the yellow banner and post a file with about 10 rows of data, showing what you want.
Did this work?
Do you really want the results concatenated into one cell (.xlsm, using a UDF is best solution)?
If you are not allowed to use a macro, or do not REQUIRE the results to be in one cell, can they be in separate cells in the same row?
I would like each wire AWG and color to be in its own cell and sorted vertically in a column. And I'm trying to get the 'layup', ex:1-2-3, to be in a separate column but next to the corresponding wire. If that's not possible, putting the layup in individual cells in the same row after the wire would also work and I could work with that.
Mmmm. Just noticed a complication:
is 2 14GA RED the same as 1 14GA RED??
yes, they are the same wire, but at two different points.
Each point has one end of the wire, so there is point 1, which shows 14GA RED going to point 2.
Point 2 shows 14GA RED going to point 1.
Hence the form Column 1 Column 2
[Current point #] [previous point #][AWG][color]
This is how we get the layup. it would be point 1->point 2
Does that help make more sense?
Note: some wires hit more than 2 points. EX: 1-2-3
Take a look at this....
Happy to explain if it is working... Not convinced, yet!!
It looks good and like something I can really work with!
In the future it's highly likely I'll be changing wire points and AWG periodically, would this reflect any changes well on a potentially expanding table?
It should be expandable without any issues. Just increase the ranges to cover the data range.
The ONLY thing to bear in mind is that the formula in D13 does not like blanks.
To make that formula fully flexible, you can use a dynamic named range to set the range automatically. Do you know how to do that? if so, fine, if not I'll do it for you.
I'm afraid I don't know how to do it, I don't have much experience with large algorithms like these in excel.
I'd very much appreciate it.
The Named range (can be found in the Name manager... CTRL-F3) is called Partials and is:
=testLink!$B$13:INDEX(testLink!$B:$B,MATCH("zzz",testLink!$B:$B))
It starts from B13 and will run down column B until it finds the last non-blank cell, and passes that range to the formula in D13, which now looks like:
=IFERROR(INDEX(Partials,MATCH(0,INDEX(COUNTIF($D$12:D12,Partials),0,0),0)),"")
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Just one more thing, When I open the file now, some parts disappear, do I need to just copy and paste the full code to the parts below it?
"some parts disappear" means nothing. Be specific. I did delete a pile of blank rows from your sample, so I could see results and raw data at the same time. But that thas been like that since the first time i posted a sheet.
sorry, not being specific is my bad habit.
in the full code, partial code, and uniques areas, the lines below row 13 became blank when I enabled editing.
i can not reproduce that. Did you look at the correct file? I checked the file that you attached at Post 17 and it is FINE.
First off. Ensure that NO copies of the file are open.
Then, re-open my file (Post 16). Does the data below row 13 still "vanish"? If so, select cell A14. Is there still a formula there? Copy paste into the forum.
This is the code in cell A13
=IFERROR(INDEX($1:$1048576,AGGREGATE(15,6,ROW($B$2:$P$9)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A12,$B$2:$P$9)=0)),1),ROUND(10^6*MOD(AGGREGATE(15,6,ROW($B$2:$P$9)+(COLUMN($B$2:$P$9)*10^-6)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A12,$B$2:$P$9)=0)),1),1),0)),"")
This is the code in cell A14 -> A33
=IFERROR(_xlfn.SINGLE(INDEX($1:$1048576,AGGREGATE(15,6,ROW($B$2:$P$9)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A13,$B$2:$P$9)=0)),1),ROUND(10^6*MOD(AGGREGATE(15,6,ROW($B$2:$P$9)+(COLUMN($B$2:$P$9)*10^-6)/(($B$2:$P$9<>0)*(COUNTIF(A$12:A13,$B$2:$P$9)=0)),1),1),0))),"")
What version of Excel are you using?
OK. Got it. Bl@@dy Microsoft and their implicit intersection @.
Just copy the formula in A13 down the column and (with a bit of luck) all will be well.
All works well!
Thank you very much!
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks