Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP with spreadsheet matching
Here's one way:
If I understand correctly, let's say the numbers are in column A on both sheets 1 and 2, and the data consists only of this column of numbers. There are headers in row 1. On the first sheet, in cell B2, put the formula =COUNTIF(Sheet2!$A:$A,A2). Copy this down the column as far as needed. Do the same thing on Sheet2: formula on that sheet is =COUNTIF(Sheet1!$A:$A,A2) Now sort Sheet 1 by column B. The rows with a 0 are the rows that will remain on the sheet, and they'll be at the top, in their same relative order. Find the first row that shows a 1 in column B and copy that row and the rows below Sheet3. (You can copy just column A if you wish.) Repeat the Sort, find-first-non-zero, Copy-to-Sheet3 on Sheet2. Then go back to Sheet1 and delete the rows you just copied; do the same on sheet 2. Then you can delete the column with the formulas on Sheet1 and Sheet2. On Sun, 7 Sep 2003 12:17:13 -0700, "D'Anne" wrote: I need desperate help in matching in an excel spreadsheet I have tab1 with data (numbers) and I want it to match to tab 2 (numbers), then if there is a match move to tab 3 (matched) the others that dont match, I want to remain on their tabs 1 and 2 Please help!!!! Thanks D. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP with spreadsheet matching
The easiest way would be on the sheet with the negative amounts, create another column that
contains the positive amount to the left, i.e. in B2, =-A2 Then search column B in the COUNTIF formula. BTW, it's best to give the full description of your problem when you first post a message, rather than a general description, then add some critical details only after somebody has worked out a solution to what you first asked. As far as a macro is concerned, how much do you know about VBA? Have you done any macros at all? You can always turn on the macro recorder and carry out the steps manually, then modify the code to eliminate hard-coded range references, etc. One can't really tell you what the code would look like without explicit information about your layout. On Sun, 07 Sep 2003 13:45:25 -0700, D'Anne Thomson wrote: Hiya and thanks, but i was wondering this: If sheet 1 is Debits and Sheet 2 is Credits and I want to match them and if the debit and credit equal zero i want to move them to sheet 3. How do I do this and make is automate (macro) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching a column of numbers to another in another spreadsheet | Excel Discussion (Misc queries) | |||
Return Data Matching Criteria in a separate spreadsheet | Excel Worksheet Functions | |||
Lookup from one spreadsheet that links to matching keys in another | Excel Worksheet Functions | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) |