Hi,
I would like to compare article numbers from 3 sheets (All_Fem, All_Tib + All_Inl) with each column A and B next to the explanation with data on sheet "Data" and, if found, write this in cell A1 in sheet "Register". A VLOOKUP function for explanation already works.
Hope I expressed myself clearly ...
Thanks
Marcus
Identifying Article numbers: these in RED ???
LOT 60829788 EDI: 630700020 REF 6307-00-020 NATURAL-KNEEŽ I| SYSTEM NONPOROUS FEMORAL COMPONENT SIZE LEFT
22012-08 +H703620008809 1/1224460771857H07S zimmer WARSAW, IN 46580. U.S.A
Catalogue Number in A
=IFERROR(TRIM(MID(Data!A1,SEARCH("REF",Data!A1)+4,11)),TEXT(TRIM(MID(Data!A1,SEARCH("+H",Data!A1)+5,9)),"0000-00-000"))
VLOOKUP
=IFERROR(VLOOKUP($A2,All_Fem!$A$2:$B$300,2,FALSE),"")
Last edited by JohnTopley; 05-03-2021 at 10:29 AM.
IN A2 of register sheet then copied across up to column B.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank you for your effort,
I have made the possible sorting of the identifying article numbers from DATA by color to the columns A, C and E in the register.
Is it possible to map the article numbers found in DATA after comparison with the article lists in ALL_Fem, ALL_Tib and ALL_Inl using formulas ??
So many thanks !!!!
yes, the first red is the correct scheme to identify the article
Hello MacDoc and Welcome to Excel Forum.
As you are using the 2019 version of Excel you could combine your tables using Get & Transform and the new table (Query1 sheet) will be dynamic as the old tables are expanded/contracted.
The Power Query advanced editor code used to produce the table is:A column (On Data) is added to the new table and populated using:Please Login or Register to view this content.Formula:
Please Login or Register to view this content.
The formula used on the Register sheet is:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Post deleted
Last edited by kvsrinivasamurthy; 05-07-2021 at 03:20 AM.
Post deleted.
Last edited by kvsrinivasamurthy; 05-07-2021 at 03:20 AM.
Here is the file with formulas .
In Register Sheet formulas are given not to have duplicate values in Columns A , C and D even duplicates are available in respective sheets.
In Register (2) sheet duplicate values are allowed. (see column C )
There are differences in formula. Select whichever is suitable.
Thank you both so much for your work!!! Its really nice.
Is it possible to expand the evaluation by querying additional information?
For this I have created a new tab with the names of companies that should be queried in "DATA" ... once for each article.
This proposal employs two helper columns (C:D) on the Data sheet.
Column C is populated using the following array entered formula**:Formula:Please Login or Register to view this content.
**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).
Column D is populated using: =IF(B1="","",INDEX(C1:C$9,MATCH("*?",C1:C$9,0)))
On the Register sheet column C is populated using: =IF(A2="","",INDEX(Data!$D$1:$D$9,MATCH(TRIM(A2),Data!$B$1:$B$9,0)))
Column F is populated by selecting cells C2:C8, pressing the Ctrl + c keys, selecting cell F2 and then pressing the Ctrl + v keys.
Column I is populated in the same manner as column F.
Let us know if you have any questions.
Thank you for your efforts... it works really nice !!
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks