hi folks
need some help on formula that will lookup material # from sheet1 into sheet2 and list the ppl listed under sheet2 column B.
if certain material # has multiple entries can you use concatenante function to add multiple names on a single row?
ie. sheet 1 cell H7 can = "Doe, John ; Maker, Justin ; Sally, Vilma" . if there no result, show blank
thx i have attached sample..pls help
feedback on this, anyone?
Hi
to be confirmed with control+shift+enter to get first 3 names (with ripetitions)
=IFERROR(INDEX('New Materials'!B$7:B$3394,SMALL(IF('New Materials'!F$7:F$3394=C$7,ROW($7:$3394)-6),1)),"")&IFERROR(", "&INDEX('New Materials'!B$7:B$3394,SMALL(IF('New Materials'!F$7:F$3394=C7,ROW($7:$3394)-6),2)),"")&IFERROR(", "&INDEX('New Materials'!B$7:B$3394,SMALL(IF('New Materials'!F$7:F$3394=C7,ROW($7:$3394)-6),3)),"")
Vba (or function TEXTJOIN) could offer a more convenient approach.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi canapone
I applied your formula in cell H7 down
however, you will notice when i filter on column G where it shows greater than 0 results, there are blanks, when it should have someones name listed. e.g. row H142, column G = 1, however, column H = blank ....it should contain a name.
thoughts? thank you
As it is array formula it takes ages to recalculate on any changes.
I think (maybe I am wrong) using VBA is a better options?
I've created something like below (it take a time also but it can be re-run only on demand):
not tested so intensively.
Please Login or Register to view this content.
Last edited by KOKOSEK; 04-17-2019 at 11:48 AM.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Hi KOKOSEK
Thanks for your reply
I pasted the code, however, it seems it's also finding names for material # that do not exist. also, as you scroll down the table, near the end, the macro seems to copy/paste all the names of people listed?
also, any chance a countifs formula can be added to column G
Some tweak added:
1st - checking only if col G shows that something exist (>0)
2nd - delete contains of already added names (that's what you probably mention about).
Please Login or Register to view this content.
OK sure, i will review and provide feedback thx
Last edited by jw01; 04-17-2019 at 12:48 PM.
OK. After evening coffee latest version, imho, quite fast:
Check attached file. Run by button as you wish.Please Login or Register to view this content.
Hi KOKOSEK
Thanks for the awesome macro!
Just wondering if i can get your help again; the workbook was changed a bit, the "new matters" is now called "data" and the two columns are A, B in data sheet.
I tried to change the range accordingly and the sheet name in the macro but getting an error:
can you pls assist? i have attached.Please Login or Register to view this content.
any thoughts on this guys?
Codes were in F so names were in (-4) column (B).
Now codes are in A and names in B, so shift +1
Formula:
Please Login or Register to view this content.
Hi
thxs for your reply
just wanted to check as it doesnt seem to be working properly.
e.g. row 22 in the "matter" sheet should show 1 in column G and show a name in column H
when i run the macro, it shows 0 and no name. Is there a column that is not being pulled properly? thxs
If you did not change matter sheet everything should be ok.
When you changing columns/row/sheet names, you have to carefully and precisely change references in VBA codes.
Start from scratch (I mean from 1 attachments with my 1st working code) and then do these changes 1 by 1 (Sheet name in excel ---> sheet name in VBA, change data column --> change its reference in VBA).
AH got it....
had to make a change here as well
Please Login or Register to view this content.
Last edited by jw01; 04-23-2019 at 11:22 AM.
Formula:Please Login or Register to view this content.
that's getting last non empty row in column A (column number 1).
You can change it into col. B -->Cells(Rows.Count, 2), or col. C --> Cells(Rows.Count, 3) etc.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks