matching a column of numbers to another in another spreadsheet

I have a spreadsheet of shortpayment amounts and their invoice numbers on one 
spreadsheet with a column of credit amounts with their invoice numbers on 
another spreadsheet. I need to match the credits written with the 
shortpayments.
kinda like this
spreadsheet 1                                               spreadsheet 2
inv #         amount     date                             Credit #     
Amount    Date
122334      15.00      11-25-09                 675555      15.00      
12-10-09
223345       22.00     10-20-09                 754444      22.00       
111-25-09

naturally there is a huge list with some credits only close to inv. short 
pays with some short pays with no credits etc. but if I can match the same 
amounts it would be a huge time saver.
0
Utf
11/29/2009 7:18:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
906 Views

Similar Articles

[PageSpeed] 43

On the face of it, to meet this aspiration
> .. if I can match the same amounts it would be a huge time saver ..
you can try a "basic" index/match, set for an exact match of the amounts col

Assume invoice data as posted is in Sheet1's cols A to C, data from row 2 
down, while credit data as posted is in Sheet2's cols A to C, data from row 2 
down. The key col is col B = amounts

In Sheet1,
In D2:
=IF(ISNA(MATCH($B2,Sheet2!$B:$B,0)),"",INDEX(Sheet2!A:A,MATCH($B2,Sheet2!$B:$B,0)))
Copy D2 across to F2, fill down as far as required. Format col F as dates to 
taste. This pulls over credit data from Sheet2 on the basis of exact "amount" 
matches in col B. This extract will work if the exact match amounts are 
unique for both invoice/credit data right through. If there could be 
identical amounts for different invoice/credit numbers, then it fails. 
Preceding helps nonetheless? hit the YES below
-- 
Max
Singapore
--- 
"newbie Annie" wrote:
> I have a spreadsheet of shortpayment amounts and their invoice numbers on one 
> spreadsheet with a column of credit amounts with their invoice numbers on 
> another spreadsheet. I need to match the credits written with the 
> shortpayments.
> kinda like this
> spreadsheet 1                                               spreadsheet 2
> inv #         amount     date                             Credit #     
> Amount    Date
> 122334      15.00      11-25-09                 675555      15.00      
> 12-10-09
> 223345       22.00     10-20-09                 754444      22.00       
> 111-25-09
> 
> naturally there is a huge list with some credits only close to inv. short 
> pays with some short pays with no credits etc. but if I can match the same 
> amounts it would be a huge time saver.
0
Utf
11/30/2009 11:10:07 AM
Reply:

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

column charts on two axis
I am trying to show a column chart on 2 axis. I have chosen the combination line and column chart to get the 2 axis but when I change the line to a second column it puts the columns over the top of the first axis columns. How do I get them to be side by side? Have a look at: http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jo" <Jo@discussions.microsoft.com> wrote in message news:F3800842-2343-4319-8496-4D8CAC0DABFF@microsoft.com... >I am trying to show a column chart on 2 axi...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Autonumber numbers
Is there a way, in a new Access 2007 database, to start the numbers of an Autonumber field at a number other than 1? It would be very convenient for us to start at 10001. Marsh An autonumber field is not supposed to be used as a user's numbering system. If you want to have a numbering system, then you should create it by creating a number field and applying your math and logic to it. This way you can start it at whatever number you wish and can control number issuing... But as far as an autonumber is concerned... you are relying on something in a way you should not b...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Is there another way to access downloaded statements in MNY 2007?
Hi, can anyone tell me of another way to get to the downloaded statements page other than having to go through the Call Summary page in Money 2007? Now, I have to click the update icon in the upper right, then "view downloaded statements". In MNY 2004, which I "upgraded" from, the program would automatically take you to the DL statement page if there was new info. Is there anyway to automate this in 2007, or to create a shortcut to the page? Thanks in advance for your help. Nick ...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Need column that will post aging date
I have spreadsheet with invoice dates on it. I need another column that will show aging date against specific date that will be input in one of the cells. Tom, Use =CellWithDate - CellWithSpecificDate like =B3-$C$1 Format for 0 decimals, and that will report days. Otherwise, you could use the DateDif function. See http://www.cpearson.com/excel/datedif.htm HTH, Bernie MS Excel MVP "tom" <Spamblocker@ameritech.net> wrote in message news:lwTdi.5139$bP5.2924@newssvr19.news.prodigy.net... >I have spreadsheet with invoice dates on it. I need > another column t...

Another Lookup Situation
Assume I have the following data that tabulates the sale of widgets b each salesperson for the firt half of the year (6 months): ******* JAN FEB MAR APR MAY JUNE Richards 10 15 20 25 30 35 Jones 2 4 6 8 10 12 Smith 1 3 5 7 9 11 Watkins 3 6 9 12 15 18 Blake 5 10 15 20 25 30 If I need to know how many widgets, say, Smith sold in the month o May, I will get an answer of 9 (This procedure I can formulate i Excel). Here is wh...

Change rows to columns for similar records
Hi - am trying to change rows to columns for similar records. How do I do that? e.g. Staff A Cellular No. XXXXX Staff A Office No. XXXX Staff B Cellular No. XXXXX Staff B Office No. XXXXX Wld like for it be in the following row Staff Cellular No Office No A XXXXX XXXXX B XXXXX XXXXXX Do advise. Thanks. one way would be Would be to set up your "heading" as such Cell Phone Staff A 1 2 Staff B 3 4 Then assuming that Staff A is in B8 Staff B is in B9 Cell is in C7 Phone is in D7 In cell c8 enter ...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

limitations of spreadsheet
I was reading a document, produced by Mathcad, on the limitations of spreadsheet for mathematical calculations. Can someone help me in this regard: 1. If I want to extend Excel for mathematical calculations, how can this be done? 2. Can Excel also be extended for graphing? Thanks in Advance -- distresses make us mistresses of our destinies. http://emekadavid-solvingit.blogspot.com On Wed, 13 Oct 2010 09:10:48 +0100, transkawa wrote: > > I was reading a document, produced by Mathcad, on the limitations of > spreadsheet for mathematical calculations. > Can someone help me i...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

Transfer In and Supplier Reorder Number
When I create a new inventory transfer in, and I add items to it, the Reorder Number column on the Content tab is never populated. I have verified that I've put a reorder number in all the places that I can find that allow me to enter one. But none of them appear in this column. The column is not populated on an export either. Is this a bug or am I missing something? I believe in order for the reorder number to populate you would have to select a supplier. Ex: if you're doing a purchase order and do not select the supplier - the reorder number will not populate. If you select t...

Copy spreadsheet into word document
I am attempting to copy a small excell spreadsheet into a word document ans save the spread sheet in landscape mode inthe word doc ...or pu another way .. I need to rotate the spreadsheet in the word doc so that I can read all the info Please give detailed instrcutions if you know how to accomplish this task.. Thanks GB You have a number of options: If it is a "small" sheet, as you say, then copy it in to the document and resize the table in the document to fit by dragging the handles. Alternatively, if that isn't practical and you need a landscape sheet in a docume...

Is there a ticket template with numbering capability?
I need to print tickets to business or post cards in Publisher 2002. I want to number them consecutively. Is there a template that will do that for me? Tonyg wrote: > I need to print tickets to business or post cards in Publisher 2002. > I want to number them consecutively. Is there a template that will > do that for me? Be sure to check out the following tutorial: http://www.publishermvps.com/Default.aspx?tabid=95 -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. I ...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

Column heading differences
The menu column headings on my spreadsheets today are numbers instead of letters! I want the letters back! I like the differentiation between rows (numbers) and columns (letters). Help?? I received and opened a virus-free spreadsheet from a co-worker. Her spreadsheet had numbers for the columns -- could that have made my default switch? How do I switch it back? <Tools> <Options> <General> tab, And *UNCHECK* "R1C1 Reference Style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benef...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

Reversed column labels
For some reason, when I open any new spreadsheets, the column labels are reversed, with column A on the right side of the screen. This is making working with excel difficult. Is there some option I enabled that can be disabled so that Excel gets back to normal? Thanks Andrew Remsen Hi Drew tools / options / international - check the settings under default direction note, you won't see the affect of this until you close & reopen your workbook. Regards julieD "Drew Remsen" <remsen59@yahoo.com> wrote in message news:MeSpd.73932$8G4.40962@tornado.tampabay.rr.com....

sort 2 column in the same time "" as dictionary ""
Hello all I need to make sort of pages to be like a dictionary is that possible I put a test file on the next link http://rapidshare.com/files/382288320/index_test.xls.html I need to sort the 2 yellow column ,to be alphapetic page by page appreciate for help me Thanks Mahmoud ...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...