• 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.

How do you Match 2 Columns Criteria and Populate the values associated?

S

smc001

Member
I have five Columns and 5,000 rows of data from a source report...


Need Search Columns(1)need to find all COMM and search column (2)Accounting Number (1011000010 and 1011000012 and 1011000100 and 1011000500)


So there's letter's and numbers that will be searched through...


Each row has a column value associated with dollar amount, which is in Column (4)


I need to search column (1 and 2) based upon above above criteria and once criteria is met it will populate the value in a new Column(6).


I suck at this! I have looked at vlookup, index, and match information and tried to copy example formula's... When I try to write the formula it's always wrong... Can someone provide a valid formula that can be used for this scenerio? I am pulling my hair out...


Thank You!
 
Hi smc001,


If I understood correctly, try this formula in column F and copy down. It will return 0 or the dollar amount :


Code: 
=AND(A1="COMM",OR(B1=1011000010,B1=1011000012,B1=1011000100,B1=1011000500))*D1


or this one, it will return the dollar amount or empty cell :


=IF(AND(A1="COMM",OR(B1=1011000010,B1=1011000012,B1=1011000100,B1=1011000500)),D1,"")


Cheers
 
Hi ,


Why can you not use an IF statement ?


=IF(AND((A2="COMM"),(OR(B2=1011000010,B2=1011000012,B2=1011000100,B2=1011000500))),D2,0)


Narayan


My post is the same as GCExcel's !
 
Thank you for the options, but I can't seem to get any of the options to work... Keep in mind this is all linked data that we are searching through... Does that have an impact?


I do get zero's but no dollar amounts...on the rows I know there's a COMM or the proper Account #... Any other suggestions?
 
Hi ,


Can you Evaluate the formula , and see at what stage it does not return the expected value ?


Are the values 1011000010 , 1011000012 ,... numeric or text ?


Narayan
 
Try the Pivot table,


In the report Filter drop Columns(1)& filter on COMM

In Row Labels drop column (2)Accounting Number in the drop down select label Filters- Contains & give the values

In Values drop Column (4)that is $


This will basically filter on all the COMM & their Accounting Number will be displayed, you are further filtering the Accounting Number & their corresponding values will be displayed
 
Try Conditional formatting, & then filter on the colors, this is the easiest way of getting the things done.
 
Unfortunetly, the source report can not be altered, filtered, manipulated... It is only for a source of data and this data is linked over to the other report that will have the formula's and do the matching and provide the dollar amounts. Any ideas how this can be accomplished? Perplexed!!!
 
I came up with this, but it doesn't work... am I close or in the right ball park?


=IF($A$2:$A$5093="COMM",OR($B$2:$B$5093=1011000010,$B$2:$B$5093=1011000012, $B$2:$B$5093=1011000020, $B$2:$B$5093=1011000200, $B$2:$B$5093=1011000500)*SUMPRODUCT(('Main Report - Master'!$K$9="Actual")))


This is the formula I put in the Main report - Master cell column titled Actual. Thats where I want the actual dollar value to populate if the source reports column A and B matches any of the above criteria. When it matches the criteria then it will pull over source reports columns 4's total dollar value and generate the total dollar amount that matches...
 
I came up with this formula... But it allows all the dollar values from Column D. I can only have the dollar values based upon my criteria... COMM in column A and Account Numbers: 10110000012,etc. in column B.... HELP!!!


=SUM((A2:A5093="COMM"),OR(=B2:B5093="1011000010"),(B2:B5093="1011000012"),( B2:B5093="1011000020"),(B2:B5093="1011000200"),(B2:B5093="1011000500")+D2:D9043)... The D cells are where the dollar values reside....


Any suggestions would be greatly appreicated...It's a bit urgent, meeting on Friday for this information... Ekkkk...Any suggestions would be greatly appreciated!!!!
 
SMC001,


can you show an example of the first 5 lines of your file, including title, separated by the | (pipe) symbol? Maybe I can play around a little bit and get back to you with some ideas...


thanks !!!
 
Column(1)ITEM NAME: TST, RSS, DVS, MSD, Etc...COMM is what I need to find


Column(2)ACCOUNTING NUMBER: all kinds of account numbers, I need to find only, 1011000010,1011000012,1011000100,1011000500


Column (3) ORDER DATE


Column (4) DOLLAR VALUE... $11.00, etc...


Column (5) DEPT NO.


Column (6) where I wanted to populate the dollar amounts
 
Hi ,


Try the following formula :


=SUMPRODUCT(((--(A71:A87="COMM"))*((--(B71:B87=1011000010)+(--(B71:B87=1011000012))+(--(B71:B87=1011000500)))))*(D71:D87))


1. Data is in the range A71:D87


2. I have included only three accounting numbers ; add more if required.


3. This need not be entered as an array formula.


Narayan
 
Hmmmm I get all zero's... Is this formula referencing column 4 which is where you would get the dollar value?
 
Hi ,


Yes. The dollar amounts are in the range D71:D87.


The range A71:A87 has the Item Names , and the range B71:B87 has the accounting numbers , which are entered as numeric data.


If you place the cursor in the cell which contains the formula , and click on the Evaluate Formula button , you should be able to step through the execution of the formula ; the check of the "COMM" item name should result in an array of TRUE and FALSE entries , TRUE where the item name = "COMM" , and FALSE where it is not.


(--(B71:B87=1011000010)+(--(B71:B87=1011000012))+(--(B71:B87=1011000500))) checks for an OR of the three conditions ; where an accounting number = 1011000010 or 1011000012 or 1011000500 ; the "+" sign does an OR. This again should result in an array of TRUE and FALSE entries.


The result of these two logical tests is now ANDed to get a final array which has TRUE wherever both conditions are fulfilled.


The last part of the SUMPRODUCT function ( *(D71:D87) ) takes those dollar amounts where there is a TRUE from the logical tests.


Narayan
 
Rats! All I get is zero's when I enter in the formula... I did fiddle around with it and got the true and false but, I didn't know I was on the right track so I deleted what I entered... I need to obtain the dollar amount... What I am I doing wrong?
 
Hi ,


I think the way forward is to have your worksheet , with at least 50 rows of relevant data , uploaded , so that we can put in the formulae and verify where the problem lies.


Can you upload your worksheet and post the link ?


Narayan
 
Here you go... Tracking Report: https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!113&parid=8C4598BED3EF132A!108&authkey=!AJ7yC0bayODTBZk


Thank You
 
Hi ,


On going through your worksheet , I find the following contents in column A :


1. COMM: CELL, PHONES,


2. CELL, PHONES,


3.  CELL, PHONES,


4. PHONES,


This is the reason we were not getting a match for "COMM" !


What is to be done ?


Narayan
 
Hi ,


This is even more surprising. All the dollar amounts are actually text strings as follows :


 $                22.00


These need to be replaced by numeric values , so that the formulae given earlier can work correctly.


Narayan
 
Hi ,


Use the following formula to convert all the text in the Dollar Amount column to numeric values :


=VALUE(SUBSTITUTE(SUBSTITUTE(D2,"$",""),CHAR(160),""))


Instead of using the values in column D of your worksheet , use the values from this new column , and everything should work correctly.


But I am still not clear about what you want ; do you want the column F to be populated with only those values which meet the 2 criteria ? If so , then the originally posted IF function should work.


Clean up the appropriate entries from column A , so that all of them follow a standardised format , add the new column converting all the dollar amounts to numeric values , and use the IF formula.


Narayan
 
The Tracking report you have reviewed is an autogenerated report which will not be altered... That why I was just sifting through and pulling the info automatically... I am not able not supposed to be changing any of the format.


Just pulling the correct data and obtaining a total value.... :) does that make sense?
 
You must log in or register to reply here.
Back
Top