• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting data after matching data by combining

paradise

Member
Dear Sir,

In the enclosed workbook,I want to extract the data in Column A3 and thereafter in Report sheet from data sheet of Column C by combinedly matching C3+D3+E3 of Report sheet with that of data of Column B of data sheet.Even though there are few more data in B column inaddition to & after combining C3+D3+E3 of Report sheet.One should have to ignore this portion as three column data combining is enough.

I will be eagerly waiting for the reply.

If you require any further information with regard to this,then kindly let me know.I know even though 100% can't be done but approx. would be suffice to me.

With Best Rgds

Suresh
 

Attachments

  • Extracting data after matching data by combining.xlsx
    56.5 KB · Views: 2
@paradise

Hi - Please see the attached...will this help...

Regards
Asheesh
 

Attachments

  • Extracting data after matching data by combining.xlsx
    80.7 KB · Views: 3
Oh God!
I did one mistake more.I forget to say that it should also match Column A of data sheet with Column of B of Report Sheet in addition to above said so far.Hence this would be multiple match.

Kindly revise your formula by taking into consideration this one more criteria to get the exact result which is currently displayed in Column A of Report.

With Best Rgds,
Suresh
 
Suresh,
I believe my file does that very thing: uses columns B:E as criteria. Note that there are some cases in your file where Report criteria is not found in Data.
This is in report (my formula shows "Not found")
upload_2014-9-25_12-44-32.png
which is confirmed when you look at Data sheet (no "TR DETERGENT")
upload_2014-9-25_12-44-50.png
 
Dear Sir,

Thank for giving me reply.You can take TR Detergent as TR and match with TR=475*60.
I have gone minutely.Only 18.50% are left to be extracted which is as mentioned by you as"Not found".I think you can decrease this percentange to lowest as far as possible by revising your formula to some extent.For example in the "Not found" part of A8,A10,A11,A12 of Report sheet has not been extracted and the likewise other can be minimised.

Here I have seen that in data sheet some where 'X' is mentioned instead of '*' and can be used as interchangebly.

Hope you can do this further and help me minimising the remaining "Not found" by revising your formula.

With Best Rgds,
Suresh
 
Last edited:
I don't know what this means. :(
Thank for giving me reply.I have gone minutely.Only 18.50% are left to be extracted which is as mentioned by you as"Not found".I think you can decrease this percentange to lowest as far as possible.For example in the "Not found" part of A8,A10,A11,A12 has not been extracted and the likewise other can be minimised.

With my formula, the extra characters aren't the problem. It's searching the Data sheet for the words, anywhere within the text. So, you can have
TR_350_55
TR=350*55
TR(350x55)
and the formula would still work, if it was looking for the 3 items: TR, 350, 55

It looks like the problem is sometimes your criteria is listed as "TR" and sometimes "TR " <--Extra space at end
I would recommend removing these extra spaces. Perhaps doing a quick Find & Replace (find "TR ", replace with "TR", match entire cell contents)?

Doing that on my end reduced the not founds to only 38.
 
Dear Sir,

Yeah you are exactly right.I am getting this rough data in 'data' sheet.The data needed to be trimmed,cleaned while employing the formula as you have said.

With Best Rgds,
Suresh
 
If you can't control data import, then we can try and 'trim' each input before use in formula. Formula in A3 becomes:
=IFERROR(INDEX(data!C:C,(1/SUMPRODUCT((data!$A$2:$A$971=TRIM(B3))*(ISNUMBER(SEARCH(TRIM(C3),data!$B$2:$B$971)))*(ISNUMBER(SEARCH(TRIM(D3),data!$B$2:$B$971)))*(ISNUMBER(SEARCH(TRIM(E3),data!$B$2:$B$971)))*ROW(data!$A$2:$A$971)))^-1),"Not found")
 
Dear Sir,

I kept your formula in A3,but it is not showing the desired result rather in each and every row it is showing "Not found".I think something is missing which I don't know.

Kindly see the attached file.

With Best Rgds,
Suresh
 

Attachments

  • Extracting data LM_2.xlsx
    64.9 KB · Views: 2
You inserted a new column, shifting your criteria into different columns. Then when you put in the formula, it was looking at wrong columns.
 

Attachments

  • Extracting data LM_2 fixed.xlsx
    64.3 KB · Views: 8
I'll give it a shot. Here's the formula:
=IFERROR(INDEX(data!C:C,(1/SUMPRODUCT((data!$A$2:$A$971=TRIM(B3))*(ISNUMBER(SEARCH(TRIM(C3),data!$B$2:$B$971)))*(ISNUMBER(SEARCH(TRIM(D3),data!$B$2:$B$971)))*(ISNUMBER(SEARCH(TRIM(E3),data!$B$2:$B$971)))*ROW(data!$A$2:$A$971)))^-1),"Not found")

The main bit is the SUMPRODUCT function. We are going to build several True/False (T/F) arrays, checking each of our criteria. THen, we will only be interested in the spots where we have a True in all the arrays.
First T/F array is (data!$A$2:$A$971=TRIM(B3)
This checks if Order # matches. The TRIM is there to remove any extra spaces, as I mentioned above were creating problems.
Next 3 arrays are a little more complicated. We need to search the particulars column to see if a value is within. The SEARCH function will return a number if a value is found, and error out if it's not found. To convert these numbers and errors into a T/F array, we put it inside the ISNUMBER function.
Second T/F array is (ISNUMBER(SEARCH(TRIM(C3),data!$B$2:$B$971)))
Third T/F array is (ISNUMBER(SEARCH(TRIM(D3),data!$B$2:$B$971)))
fourth T/D array is (ISNUMBER(SEARCH(TRIM(E3),data!$B$2:$B$971)))

Now we have all our criteria being checked, hurrah! But, what to do with this information? We need to know which cell, aka which row the trues all lined up in. So, the last array is a list of the ROW numbers.
ROW(data!$A$2:$A$971)

So, when all the criteria are met, the Trues line up, and when you multiply, XL converts T/F into 1/0. So, multiplying the T/F arrays against the ROW array will produce a number, either 0 (if not found) or a row number (if found). The SUMPRODUCT then does the sum portion of it's name, and reduces the arrays to a single number.

Next, we need to check if no match was found. As I just said, if there's no match, then we have a 0 at this point. To check for this, I do a little "math magic". If you look at this math:
(1/x)^-1
We can see that that is equivalent to just x, so it at first seems pointless. The benefit in this case, is that if X = 0, we'll cause a DIV/0 error, and we can trap that with the IFERROR function. So, if X <> 0, it passes out math check, and then the INDEX function takes the row number, looks in col C of the Data sheet, and returns the correct cell. If X = 0, we caused an error, and the IFERROR function returns the "Not found" message.
 
Back
Top