|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 29th, 2009, 08:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Matching 2 columns
Column A and column B are both 10 positions long.
1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A. 
|
|
#2
April 29th, 2009, 09:29 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
Try these array formulas** :
Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1  1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#3
April 29th, 2009, 09:45 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
It depends on what you want your list to be like. What are you doing with the values that are missing? A very simple way to find missing values is to use countif... Assuming your data is in rows 1-10 of columns A&B, then paste this formula into cell c1 and copy down =COUNTIF($A$1:$A$10,B1) This counts how many times the B column value for that row appears in column A. Any rows with a count of zero indicate a missing value. It's not a true list like an array but is much more intuitive. Cheers, Glen "Vic" wrote: Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#4
April 29th, 2009, 09:50 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
My column A ranges from A2 thru A1274 and column B is from B2 thru B1003.
I can't figure out how to adjust these formulas. The first rows contains headings. Please help. "T. Valko" wrote: Try these array formulas** : Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1 1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#5
April 29th, 2009, 09:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
Here is what I need. I have 1273 invoices. My service provider has 1002
invoices. We don't match. I need to find invoices that he does not have. I also need to know of invoices that he has but I don't. I don't care about any duplicates. I want them gone. I do not need to count anything. I need to produce a list of unique invoice numbers. Invoice numbers are 10 alphanumerics in length (not really numbers). Please help. "Glen" wrote: It depends on what you want your list to be like. What are you doing with the values that are missing? A very simple way to find missing values is to use countif... Assuming your data is in rows 1-10 of columns A&B, then paste this formula into cell c1 and copy down =COUNTIF($A$1:$A$10,B1) This counts how many times the B column value for that row appears in column A. Any rows with a count of zero indicate a missing value. It's not a true list like an array but is much more intuitive. Cheers, Glen "Vic" wrote: Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#6
April 29th, 2009, 10:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
What's the next thing you need to do with the missing data? Which formula
can't you figure out, mine or T. Valko's? For mine, it bceomes =COUNTIF($A$2:$A$1274,B2) put this is c2 then copy and paste down to row 1003. I would think twice about including formula in a spreadsheet that are too complicated for you to edit or you risk having a spreadsheet that you can't do anything with the next time something changes. Cheers, Glen "Vic" wrote: My column A ranges from A2 thru A1274 and column B is from B2 thru B1003. I can't figure out how to adjust these formulas. The first rows contains headings. Please help. "T. Valko" wrote: Try these array formulas** : Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1 1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#7
April 29th, 2009, 10:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
This formula counts something. I need the actual values. For example:
If value of A2 is not found in B2 thru B1003, I want the value of A2 to appear in C2. Also if B2 is not found in A2 thru A1274, I need the value of B2 to be placed in D2. If anything in A and B match, I don't want that - It means that they are already reconciled. Thank you. "Glen" wrote: What's the next thing you need to do with the missing data? Which formula can't you figure out, mine or T. Valko's? For mine, it bceomes =COUNTIF($A$2:$A$1274,B2) put this is c2 then copy and paste down to row 1003. I would think twice about including formula in a spreadsheet that are too complicated for you to edit or you risk having a spreadsheet that you can't do anything with the next time something changes. Cheers, Glen "Vic" wrote: My column A ranges from A2 thru A1274 and column B is from B2 thru B1003. I can't figure out how to adjust these formulas. The first rows contains headings. Please help. "T. Valko" wrote: Try these array formulas** : Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1 1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#8
April 29th, 2009, 10:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
My column A ranges from A2 thru A1274
and column B is from B2 thru B1003. Hmmm... Column A and column B are both 10 positions long. The formulas I suggested will work but they'll be slow to calculate on ranges that size. In the formulas replace all references of: A1:A10 with A2:A1274 B1:B10 with B2:B1003 -- Biff Microsoft Excel MVP "Vic" wrote in message ... My column A ranges from A2 thru A1274 and column B is from B2 thru B1003. I can't figure out how to adjust these formulas. The first rows contains headings. Please help. "T. Valko" wrote: Try these array formulas** : Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1 1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#9
April 29th, 2009, 10:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
OK. Makes more sense now. The steps below will give you two lists, with
missing only (no duplicates etc). Menu locations will work for Excel95-02. IF you have Excel07, you'll have to work them out but should be similar. *Copy the two columns into a new sheet First sheet will find missing values in column B: *Paste this formula into cell B2 =COUNTIF($A$2:$A$1274,B2) *Copy and paste the formula all the way down to row 1002 in column c. *Select column c. *Turn auto filter on (menu is |Data|Filter|Autofilter|) *click the little drop down arrow at the top of column c and select Custom... *change first filter option to read "Does not equal" and 0, click OK. *select all (Ctrl+A) and then hit delete. *select column A and delete *select column C and delete *select column B and sort (little A-Z button on menu or else |Data|Sort| and OK. The only thing left now should be missing invoices in column B. Second sheet will find missing values in column A: *Paste this formula into cell B2 of the second sheet =COUNTIF($b$2:$b$1002,a2) *Copy and paste the formula all the way down to row 1274 in column c. *Select column c. *Turn auto filter on (menu is |Data|Filter|Autofilter|) *click the little drop down arrow at the top of column c and select Custom... *change first filter option to read "Does not equal" and 0, click OK. *select all (Ctrl+A) and then hit delete. *select column B and delete *select column C and delete *select column A and sort (little A-Z button on menu or else |Data|Sort| and OK. The only thing left now should be missing invoices in column A. Hope this helps. Glen "Vic" wrote: Here is what I need. I have 1273 invoices. My service provider has 1002 invoices. We don't match. I need to find invoices that he does not have. I also need to know of invoices that he has but I don't. I don't care about any duplicates. I want them gone. I do not need to count anything. I need to produce a list of unique invoice numbers. Invoice numbers are 10 alphanumerics in length (not really numbers). Please help. "Glen" wrote: It depends on what you want your list to be like. What are you doing with the values that are missing? A very simple way to find missing values is to use countif... Assuming your data is in rows 1-10 of columns A&B, then paste this formula into cell c1 and copy down =COUNTIF($A$1:$A$10,B1) This counts how many times the B column value for that row appears in column A. Any rows with a count of zero indicate a missing value. It's not a true list like an array but is much more intuitive. Cheers, Glen "Vic" wrote: Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
|
#10
April 29th, 2009, 11:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching 2 columns
This formula counts something. I need the actual values. For example:
Yes, but then the rest of the steps deleted everything where there was something to count, leaving only the unmatched invoiced numbers. If value of A2 is not found in B2 thru B1003, I want the value of A2 to appear in C2. It pays to explain exactly what you need - I could have done this in the first reply and had the answer to you ages ago :-( Put this formula in cell C2 =IF(ISERROR(MATCH(a2,$b$2:$b$1003,0)),a2,"") and put this formula in D2 =IF(ISERROR(MATCH(B2,$A$2:$A$1274,0)),B2,"") paste them both down. Only missing invoices will be shown. Cheers, Glen Also if B2 is not found in A2 thru A1274, I need the value of B2 to be placed in D2. If anything in A and B match, I don't want that - It means that they are already reconciled. Thank you. "Glen" wrote: What's the next thing you need to do with the missing data? Which formula can't you figure out, mine or T. Valko's? For mine, it bceomes =COUNTIF($A$2:$A$1274,B2) put this is c2 then copy and paste down to row 1003. I would think twice about including formula in a spreadsheet that are too complicated for you to edit or you risk having a spreadsheet that you can't do anything with the next time something changes. Cheers, Glen "Vic" wrote: My column A ranges from A2 thru A1274 and column B is from B2 thru B1003. I can't figure out how to adjust these formulas. The first rows contains headings. Please help. "T. Valko" wrote: Try these array formulas** : Items in A that are not in B: Array entered in D1: =IF(ROWS(D$1 1)=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,S MALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A $10)),ROWS(D$11))-MIN(ROW(A$1:A$10))+1),"")Copy down until you get blanks Items in B that are not in A: Array entered** in E1: =IF(ROWS(E$1:E1)=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,S MALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B $10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vic" wrote in message ... Column A and column B are both 10 positions long. 1) I need to list all entries in A that are not in B 2) I also need to list all entries in B that are not in A.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
