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

I think what Narayan is saying is that you won't be able to obtain a total value if all of your number values are identified as text. Excel won't process them because it's essentially the same as adding the letter a to the letter f. Makes no sense. If you can at least convert your format over, Excel can recognize them as numbers. Additionally, column A should be cleaned up and "normalized" for any of your formulas to produce a "total value".
 
I have created a conversion column and changed the characters to numerical value. Works Beautifully!!!! Then I have entered into the very next column the formula suggested that searches for the particular description and should return the dollar amount associated with the descrition which resides in another column... At this point all I get are zero's even though I have referenced the newly added text to numeric column...


As for normalizing column "A" this is the data that is autogenerated report (I can't touch) I have to work with and I am requesting guidance on how to search on specific descriptions within the (2) columns of data with description I can use... which inturn would produce from the matching columns row the dollar value associated with the matching description criteria...


Is this not a possible feat.? Obviously I am really missing something...any and all suggestion are welcome.
 
Hi ,


Check out this uploaded worksheet. The formulae are in columns F through J.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21118


Copy the entire address and paste it in your browser , instead of clicking on the hyperlink.


Narayan
 
Hi ,


I should think so. Unless you wish to use those columns for other formulae for your use. The formulae in columns G through J can be anywhere else on your worksheet. The formulae in column F reference these other formulae ; in case you change your columns , change the formulae in column F accordingly.


Narayan
 
The end goal here is that the dollar value is generated if matching the criteria, which will be linked to a final report...


So, it's not really an easy fix to extract the information I need to populate in a specific cell in a final report. Rats... What happend to good ole ODBC abilities... The autogenerated report does not allow for anyone to connect via ODBC... rotten... Any other suggestions??? I am clueless when i comes to Macros... and lame when it comes to excel formulas...
 
Hi ,


No issue needs to be given up as beyond a solution !


If you can upload a complete worksheet , and specify exactly what the end requirements are , you should be able to get a solution from someone in this forum.


Can you try ? As far as I can see , most times , a problem is brought up , and then it starts going in different directions along the way ! If specific points are taken up , and handled , one after the other , any problem can be resolved satisfactorily.


Narayan
 
May be I am not making any sense…I did provide an example:


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


As you suggested I added the Conversion formula (text to numbers): =VALUE(SUBSTITUTE(SUBSTITUTE(D2,"$",""),CHAR(160),"")) in last column G…I got the dollar amounts.. :)


Then in column H tried to input your formula and got all zero’s: (--(B71:B87=1011000010)+(--(B71:B87=1011000012))+(--(B71:B87=1011000500)))


What am I doing wrong?


I need it to find all the COMM, Software, etc… Once the COMM or Software, etc… is identified I need the dollar amount for that line item to be linked or pulled over into another report….


Once all the COMM, Software, etc is found I need to total each one altogether ( If I find 3 COMM I need to give me ( Question would I total in the source report or in the Main Report) the dollar amount for each of the 3 and then it will be totaled altogether. Same for Software, etc….


If it’s totaled in the source the total would be linked over to a main report under the Communications Total Cell that’s located in another workbook, same for Software, etc….


Does that make any since Please advise and thank you again for your guidance…
 
Hi ,


Sorry for the delayed reply. Can you check out the worksheet at the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21121


Narayan
 
I have looked it over and it's a very elaborate piece of work... Most impressive!!!


Just clarify thatall of the cells to the right of the Value Populate column are the formulas that will need to be added on the report each day a new report is genarated so I may extracting the proper data I am searching for (value populates column)?


Or am I missing something? Hey I just wanted to thank you... I wish I understood this stuff better. If I did then it would not be so painful for the person help me...
 
Hi ,


Yes. You are right. Thanks for the appreciation.


I think if you break down the problem into small chunks , it all becomes that much easier to understand and resolve.


Narayan
 
This is a goofy question but since I am very new to this can you explain how the Accounts_Table,2,FALSE is used... I see Accounts_Table and it lists alot of descriptions...Column one and column two of the table are not always the same???...


What does column one represent and what does column two represent? Thank You in advance for clarification....


If I was to add to this table, which column would I add the desciptions that are associated with the main category.. Column 1 or Column 2...


Example: Travel (Main Category): column 1? and variations of Travel; car rental:column 2? taxi fees: column 2? airline tickets: column 2?, TVL Commerical, Column 2?, TVL in-state, column 2?


Please advise...or am I over thinking this? Thank you in advance for your immediate clarification....
 
Hi ,


The Accounts Table has been used to standardise on the descriptions ; as mentioned earlier , there were , I think , 4 descriptions ( " CELL, PHONES" , "CELL, PHONES" , "COMM: CELL, PHONES" , "PHONES" ) , all of which represented the same account category.


I narrowed down the list of differing descriptions to these ( the descriptions in column 1 of Accounts Table ) , by using the remove duplicates feature of Excel. After getting the list of unique account descriptions , I then clubbed these similar ones together , by giving them an identical description in column 2.


By looking up the actual entered account descriptions , in this table , and by putting in the standardised account description , taken from column 2 of the Accounts Table , we are sure to get every amount in the data in the final report.


If you come across any new account categories in future , you could add to this Accounts Table , by entering the various versions of this category in column 1 , and putting the standardised description of these categories in column 2 ; for example , suppose your main category is Travel , and the various ways of entering this in the actual data would be "car rental" , "taxi fees" , "airline tickets" , and so on , then these differing versions i.e. "car rental" , "taxi fees" , "airline tickets" , and so on , would appear in column 1 of Accounts Table , while the corresponding cells in column 2 , would all have the description "Travel". However , whether to name these as a separate category "Travel" , or to accommodate them under some existing category "TVL I/S: RENTAL CAR" , is your decision.


You will have to change the range addresses for Accounts Table from the present AC1:AD21 to accommodate the additional entries you may make.


Sorry about the delay , mostly because we are in different time zones !


Narayan
 
Can this setup apply if I have to search for categories specific total values?


Lets say all travel and obtain total dollar value... Then I need to find all communications total dollar value... Then I need to find all Hardware total dollar value....


I was looking at the following formula and I was wondering if I expand the table with new categories and description... As well expanding the columns you have referenced to list all of the categories...


This is what I have done by adding this to the last columns of the source report I needed to gather the data.


Column AC (Value Populates)


=IF(OR(S2=50100,S2=5010010,S2=5010050,S2=5030090,S2=50100900000001),AF2:AQ2,"")I changed the AH2 you had reference and expanded it out to the added columns-AF2:AQ2. Can this work I am not seeing it work...


I have also added to the table and made sure it captured the expanded table...
 
Hi ,


In the uploaded worksheet , the dollar amounts ( as numeric values ) , were in column H. The columns I through Z were the different account categories. Have you retained this same structure ?


In case you have added more categories , I assume they would extend beyond column Z , to say column AB ( just for instance ) ; in this structure , just a simple =SUM formula , at the bottom of each column data , would give the total for each category. Is this what you want ?


In the formula you have posted , AF2:AQ2 on its own would not be correct ; do you mean to sum the values in the range AF2 through AQ2 ? In such a case , use SUM(AF2:AQ2) instead as follows :


=IF(OR(S2=50100,S2=5010010,S2=5010050,S2=5030090,S2=50100900000001),SUM(AF2:AQ2),"")


However , I think , summing across the columns would return only one value ; if you really need a total of any one category , say COMM , then summing across the rows , in the COMM column , something on the lines of :


=SUM(AJ2:AJ1039)


would give you the result. I assume that the COMM category is in column AJ , and your data extends from cell AJ2 through AJ1039.


Narayan
 
Structure...hmmmmm of course....


This is what it looks like now...


Source Report Random Auto Generated Information (Columns A-AA),


Source Report: Columns which we are Scanning for a match:

E2 (USER NAMES)

G2 (TASKS)

J2 (DEPARTMENT ASSIGNED)

K2 (DESCRIPTIONS)

O2 (DOLLAR VALUE)

S2 (ACCOUNT CODES)


In the Source Report after Columns A-AA information I added to the far right Columns AC-AQ Your beautifully constructed formula's... They look great on your example and not so much on mine when added.. ;(


Columns AC-AQ

AC (VALUE POPULATES)

AD (CORRECTED ITEM NAME)

AE (AMOUNT)


CATEGORY NAMES ARE THE REMAINING COLUMNS AF(CAT1)-AQ(CAT12)


Okay and then with your awesome table I created wks1 separate worksheet within the same workbook... I created the following defined tables (two column)in wks1 to reference

Column D & E (USER NAMES),

Column J & K (TASKS),

Column G & H (DEPARTMENTS ASSIGNED),

Column A & B (ACCOUNT _TABLE)-for DESCRIPTIONS)


Heres the formulas in each column you had advised to add to the far right in the source report:


AC (VALUE POPULATES):

=IF(OR(S6=50100,S6=5010010,S6=5010050,S6=5030090,S6=50100900000001),AH6,"")


Can I have it run through each column we are seeking a match and if it's a hit it will populate?in AF:AQ instead of AH column which is COMM...


AD (CORRECTED ITEM NAME):

=VLOOKUP(S14,Accounts_Table,2,FALSE)


I want to add the additional tables I created - HOW? as well couldn't I just reference wks! instead of a table name? Obviously I have been reading too much and my facts are jumbled... ;)


AE (AMOUNT):=VALUE(SUBSTITUTE(SUBSTITUTE(O2,"$",""),CHAR(160),""))


(I have gotten $dollar amounts or #Value)


Break down formula below applied to columns AF-AQ


IF(CORRECTED ITEM NAME=CAT1,AMOUNT,zero)


(Out of 1200 rows I have not been getting many zero's mostly #N/A's)


AF (CAT1):=IF($AD2=AF$1,$AE2,0)

AG (CAT2):=IF($AD2=AG$1,$AE2,0)

AH (CAT3-COMM):=IF($AD2=AH$1,$AE2,0)

AI (CAT4-TRAVEL):=IF($AD2=AI$1,$AE2,0)

AJ (CAT5-TRAINING):=IF($AD2=AJ$1,$AE2,0)

AK (CAT6):=IF($AD2=AK$1,$AE2,0)

AL (CAT7):=IF($AD2=AL$1,$AE2,0)

AM (CAT8):=IF($AD2=AM$1,$AE2,0)

AN (CAT9):=IF($AD2=AN$1,$AE2,0)

AO (CAT10):=IF($AD2=AO$1,$AE2,0)

AP (CAT11):=IF($AD2=AP$1,$AE2,0)

AQ (CAT12)=IF($AD2=AQ$1,$AE2,0)


Can you review and let me know where I messed up.... Thank You!
 
Hi ,


Thanks for the detailed post ; however , if you don't mind , can you either upload your file or let me have it by email ( narayank1026@gmail.com ) ?


Going through your post , visualising the worksheet cells / columns , will take a little longer ; if I can make the changes within the worksheet based on your requirements , I think I can do it in less time. If you don't mind the delay , I will reply based on the details you have given in your post.


Narayan
 
You must log in or register to reply here.
Back
Top