hopefully someone can help me, i have tried a few different array formulas, but nothing seems to get exactly what i need.
i need to match a certain word from column a with its matching word (can occur multiple times) in column i with the adjacent word in column j.
i have not been able to do this accurately.
can anyone help?
thanks in advance.
Last edited by Declan.Ryan; 11-09-2012 at 08:52 AM.
Please upload a sample workbook.
this one has headers: example1.xls
Last edited by Declan.Ryan; 11-09-2012 at 05:41 AM. Reason: better attachment
how the multiple records should be handled? I column has three Abergavenny, whereas A column has only one. Which value you to want to lookup?
Check the attached sheet. I have used named formula.
Try the below code in standard moduleOriginally Posted by Declan.Ryan
Function GetMatches(LValue As Range, LRng As Range, ResRng As Range) As String Dim i As Long For i = 1 To LRng.Cells.Count If WorksheetFunction.CountIf(LRng, LValue.Value) Then If LRng.Cells(i).Value = LValue.Value Then GetMatches = GetMatches & ResRng.Cells(i).Value & " " End If End If Next i GetMatches = Trim(GetMatches) End Function
=GetMatches(LookupValue,LookupRange,ResultRange)
In B1 cell
=GetMatches(A1,E1:E12,F1:F12)
Drag it down.
Refer the attached excel for details.
Edit: Protect the range =getmatches(A1,$E$1:$E$12,$F$1:$F$12)
Last edited by :) Sixthsense :); 11-09-2012 at 06:24 AM. Reason: Code Tag Applied
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
this seems to be working, is it normal for computers to really struggle with modules? excel is using 100% of the cpu at the moment.
i'll give it a while until it's stopped freaking out.
I need to be able to list next to the Abergavenny in column A what is next to each Abergavenny in column I
I got this formula from this forum.. See attachment.. Hope it helps,
Click * to reward me...
Thank you...
Unfortunately that is not what i am looking for, thank you for the help though.
the formula itself would need to be in column b and list all of the adjacent TOCs to the matching train station.
apologies for my lack of knowledge, i am determined to learn!
Doesnt seem like the named formula is working either, seemed fine when you sent it to me but is not working now
Are you using excel 2003 version?
yes
(need 10 characters)
in the named formula, i have hard coded no. of rows in i and j column. It will take only upto 13. Since you have mentioned TOC upto 7, that is also hard coded. Can you modify that?
And iferror formula won't work in your version.
after trying out with module codes, i dont think they are an option anymore, my computer just cannot handle it.
is there a way to do it without modules?
yes i modified it, but as you said the iferror formula will not work with my version.
Use this instead of that formula.Formula:
=IF(ISERROR(INDEX($J$2:$J$13,Find_Match)),"",INDEX($J$2:$J$13,Find_Match))
Select B2:H2, enter this formula, confirm it with Ctrl+Shift+enter
Then you can copy to other cells
thank you so much for your continued help, unfortunately this hasn't worked either, when using it on the spreadsheet with 3500 rows it just comes out blank on every cell.
example2.xls
here is a larger example in hope that it may help
Last edited by Declan.Ryan; 11-09-2012 at 08:04 AM. Reason: spelling error
Check the attached file. It works.
The named formula is also array formula. You need to confirm it with ctrl+shift+enter.
hurruh! that works perfectly, thank you so much for taking your time out to help me. hope to be able to do the same one day.
thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks