In an array, one column contains strings which are not unique - some are repeated. Similarly, another column contains repeated strings. However, any single row combination from column 1 and column 2 IS unique. I need to find this row when a VBA function is sent the two strings, one for each column. Any ideas? I'm getting desperate.
Thank you.
If you sort by col 1 and col 2, then you only need to compare adjacent rows to know if a given row is unique:
Row\Col A A C2: =((A2<>A1)+(B2<>B1))*((A2<>A3)+(B2<>B3)) > 0 A C A C A C A D A D A D A E A E B A B A B B B C B D B D
Last edited by shg; 05-02-2015 at 01:49 PM.
Entia non sunt multiplicanda sine necessitate
Many thanks for the reply - any chance of starting me off with some code or a little more detail? Still not sure how to approach it.
The posted example?
That should help, thank you so much. I need to construct this as a vba function so I will try it out and post back soon...
SHG, thank you again. I have to leave this for a few hours but PLEASE could you keep the thread open for a while? I'll be working at it later tonight (UK time).
You could also just select both columns and do Data > Remove Duplicates.
I may have misunderstood, but I don't think I can use the formula or options given. The data in the two columns is different and can never match.
The pairing of data is unique, but not a match. A function should send two parameters (strings) which must match two cells in the same row.
The only way I can see of doing this is by looping down each column until the parameters match the cells, but I thought there might be a more elegant method.
I have to leave this for tonight as I'm beyond thinking clearly but I may try tomorrow, or use the paid service if I can't sort it.
Goodnight from the Great State of Confusion, UK.
Paying for a solution isn't going to avoid the necessity of explaining clearly what you're trying to do. Maybe this:
Row\Col Bob Ben Bob Joe F1: =MATCH(1, INDEX((A1:A10=D1)*(B1:B10=E1), 0), 0) Bob Anne Bob Bill Ben Joe Bob Joe Bob Beth
Last edited by shg; 05-02-2015 at 07:39 PM.
The spreadsheet lists book titles in one column and formats in another. There are three formats, so a title appears three times, each with a different format.
Here is a screen shot:
Screenshot 2015-05-03 07.56.25.jpg
I tried converting the last formula you gave me to vba code but still got the same error message 1004, "Unable to get the index property..."
This is the test line I used:
So I am feeding through the title and want the function to return the row number where, in this case, it is a "Paperback", e.g Robinson Crusoe would return 5.
Please Login or Register to view this content.
Thanks!
Time to post a workbook.
It's 9 years of work, overly complicated and too big to attach. I tried to distill the problem down previously and I have a clumsy looping function which works, but I know there's something more elegant out there. If you see it, let me know... if not,thanks for trying, and apologies for not explaining it clearly first time.
Row\Col Alien DVD Alien Hardcover H3: =TheRow($B$2:$D$8, "Title", F3, "Media", G3) Alien Hardcover Aliens DVD Alien Paperback Aliens CD Aliens DVD Aliens Hardcover Aliens Paperback
Please Login or Register to view this content.
Thank you so much for helping out with this problem and for being so patient. It's perfect. At least, I'm sure it will be once incorporated into the workbook. My function wasn't too far away, so that cheered me up, but this is very neat.
You're welcome.
A little clean-up:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks