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

What Formula is the Best Approach to this Problem

S

smc001

Member
I have a main report that will be linked to another tracking orders report for a link data pull into the main report (all departments dumps data into when ever a new request is made. I know it would be nice if they had a database, but I am not as lucky... My report has specific verbage that I feel I can use to find the association between the two and isolate, which row I can use to pull data.


In my thought process is just like fnding the information: description, region and dollar amount that match the Main reports information.. It may not be identical but, its pretty cloas to the original...


I know you would think there should be some sort of number assignment for tracking purposes and yes there is a number assigned at a later stage... My report speaks to projection and actual expenses that have taken place over the past fiscal or may be processed and assigned a number tomorrow...Ultimate goal is to get the dollar amount spent...


The report which feeds has the same Desciption of product, department that has placed the order and the dollar amount of the product and a log #... I need a formula that can search the data report and when one or a few of those criteria's match and populate the dollar amount spent onto my main report....


Anyone have any suggetions? Thank you in advance for your time.
 
To grab a number from one cell at a time on the report you are linking to, using the INDEX and MATCH functions together is an appealing solution. It supports wildcards (* and ?).


Definitely the fact if I understand you correctly, that the identifying, key info is not exactly the same poses some challenges, and to a lesser degree of challenge the fact that you have several criteria to match...


Some methods of using INDEX/MATCH with multiple criteria are listed in this article:

http://support.microsoft.com/kb/214142


~~~~~~~~~~~~~~~

With the exact logic of how to find the data you want, there may be different or more details to the suggested solutions. If the matching data may not be identical, you need to identify all the ways in which it could be different, but still be a match, and have your formula calculate information that WILL match exactly (or possibly with the aid of * and ? wildcards). For speed, the calculated exact match "key" should be in a separate helper column in your spreadsheet (and ideally also in the linked report, if possible).


Asa
 
I actually have five data reports to pull the information from and see which of the five data reports has the information that would apply to the item listed on the dept. order tracking report. On the order tracking report I have a section Actual Dollar Amount, which will be populated once I can create the correct formula link. I am just trying to obtain the Actual Dollar amount listed on one of the five data reports.


So I need something that can filter through each of the five data report workbooks and pull over the correct dollar amount associated with my item listed on the dept. order tracking report.


The five data reports will change names bi-monthly or monthly, which I have no control over since it's coming from another dept. which has its own unique name conventions...


When I review description or titles within each (five reports and dept. order tracker) I can only determine the only similar factor would be the description of the item ordered. This is the follow columns on the five data reports:

A. Vendor (Vendor #)

B. Text (Invoice #)

C. Commitment item name (Special Name Convention to Five Data Report)

D. Commitment Item (Special Item Name Convention to Five Data Report)

E. Ref Document Number (Assigned by other depts.)

F. Predecessor doc.no. (Assigned by other depts.)

G. FM posting date

H. Payment budget Dollar amount

I. Funds Center (acct number assigned to dept.)


I need to filter through the five data reports based upon the listed dept. order tracking reports item description, so I can obtain the dollar amount. That’s all I am really trying to gather is the actual dollar amount charged.


on the order tracker I only have an

1. item description.


I am may need to add another column for ref doc# or predessor# an order confirmation so it can search as well for a match.


What would a formula look like to have it search five data reports and match a description/ref doc#/ or predessor# to provide the actual dollar amount into the order tracker report? thanks inadvance for any input!
 
Hi ,


I am not able to visualize the problem , let me put it in my own words. Please confirm / clarify.


1. You have a cell in your order tracker worksheet , which contains text , signifying the item description. You may also add another cell or two more cells , which will contain text / numbers (?) , signifying the Reference Document No. and Predecessor Document No.


2. Some ( or all ) of these cells have to be used to search in five other worksheets.


3. Is it guaranteed that a match will be found in only one worksheet ? If a match is found in more than one worksheet , what is to be done ?


4. For matching any or all of these cells , which are the target cells to be searched in each of these 5 worksheets ?


5. Once a match is found , which cell will contain the Dollar Amount to be returned ?


Narayan
 
1. You have a cell in your order tracker worksheet , which contains text , signifying the item description.

Yes...


You may also add another cell or two more cells , which will contain text / numbers (?) , signifying the Reference Document No. and Predecessor Document No.

Yes...


2. Some ( or all ) of these cells have to be used to search in five other worksheets. Yes...


3. Is it guaranteed that a match will be found in only one worksheet... Hopefully (There should only be one since there is only one order associated)


If a match is found in more than one worksheet , what is to be done ? Good Question...


4. For matching any or all of these cells , which are the target cells to be searched in each of these 5 worksheets ? The target cells in the Five Data reports would have a description possible the other two identifying columns (Reference Document No. and Predecessor Document No.)


Once a match is found , which cell will contain the Dollar Amount to be returned ? The cell that would contain the dollar amount to receive this information is cell K31-37, K51-K59, etc... This is on the order tracker report/worksheet and a quit a few other K's down the line since they are sectioned up by departments ordering various products.
 
Hi again smc,


My biggest concern is that you said the description "may not be identical", if I understood your original post correctly.


It would be very helpful for some examples that demonstrate the range of differences that you want to be automatically matched. Can you list some descriptions you'll have, and the ones they should match in the reports?


Thanks-

Asa
 
Same goes for any other fields you may want to include in the the comparison if the description will not be sufficient on it's own for a unique match.


Asa
 
Yes on the description cell it's at a granualary level for the field office that has entered the request for payment (examples: travel to pittsburg, or lodging at Motel 6 or printer purchase/box of 8x11 paper) and on the source report that the comparision is on a corp level of terminology description: Like (Travel, lodging, office supplies), which has been billed.


Oh, as I look at this way I suppose if we added travel for a description criteria to be associated with travel and just find travel entree's that would work? Granular description vs common name or category description? Is that possible?
 
Hi smc,

The problem with your problem, as I understand it, is that it requires associating purchase order items with financial accounts... usually a job for accountants, not spreadsheet formulas.


If these expenses have already occurred in the past, .. you mentioned in your first post "last fiscal" (perhaps as an example), then surely they have been "accounted for"? Really the best solution is to get the data with the account assignments.


Barring acquiring more appropriate data to analyze, what if you extract all the items from the linked sheets, create a unique table of items, and add another column that specifies the account for that item. Then we can cross-reference your data.


Are the various payable items consistent enough that that would work?


I'm not sure * and ? wildcards would work out well enough.. they are not capable of describing text in great detail, especially when it may need to be distinguished from other similar text.

Oh, as I look at this way I suppose if we added travel for a description criteria to be associated with travel and just find travel entree's that would work? Granular description vs common name or category description? Is that possible?
Click to expand...

Well,

Code: 
MATCH("*travel*",range,0)

Will find all cells in a table with "travel" anywhere in the text. These would all match:

travel

TRaveL

traveles

Gulliver's Travels

Well-Travelled in Saipan: The Book


~~~~~


I will try to help you with this, but my time is pretty short right now....


Asa
 
Please humor me Where would I place the formula in the report? The last blank column on the far right of the reported data?
 
Hi, smc -

Happy holidays & new year!


I have some pressing matters consuming my time this season, so I hope you have been able to get any needed assistance from others.


You may want to start a new topic if you still need help. You can try to reframe your question if needed, and you can link to this or other related topics for your helpers to refer to. I keep track of the topics I've participated in and when I have the time and energy I will try to help out.

Asa
 
No Worries! God Bless and thank you for all your assistance!!! Happy Holidays and May the New Year bring Great Prosper and blessings to Us all!
 
SMC maybe i can pick up from here. however, i cannot upload spreadsheets from where I am today, and this would easier to explain with an example. forgive me if i'm telling you what you know.


I think what asa is trying to do is use the MATCH function to find the key string 'travel' in a range. If MATCH finds 'travel' in any string then it will return the ROW index (or number). It doesn't matter where you put the MATCH function, first column or last column.


MATCH is really useful when combined with INDEX. INDEX(...) can be used to return a single value (Cell) in an array or a range of values (cells). you have to provide the details for what you want to return: so you need to embed the MATCH function into the INDEX function.

e.g. INDEX (ReturnRangeSpreadsheet1 , MATCH ( "*travel*",LookupRangeSpreadsheet1,0), 1, 1 , 5) would look for 'travel' in the LookupRangeSpreadsheet1 (for example could be bg15:bg250) and return the row number. This would then return an array of 1 row and 5 columns from the ReturnRangeSpreadsheet1 array.

this is a great article on INDEX from excelhero: http://www.excelhero.com/blog/2011/03/the-imposing-index.html
 
You must log in or register to reply here.
Back
Top