| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 columns and align duplicates into same row
Hi
Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare 2 columns and align duplicates into same row
Try this play ..
Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare 2 columns and align duplicates into same row
Thanx Max for your help, but it isnt working how I intended it to.
I wanted to be able to match up the Bounced emails in Column "C" with the emails in column "B". Your formula has marked the bounced emails with an "x" in column "D" and when sorting hasnt moved them next to the duplicate email in Column "B". Is there something im doing wrong or is the formula incorrect? Sorry for the inconvience and thanx for your help in advance. "Max" wrote: Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare 2 columns and align duplicates into same row
match up the Bounced emails in Column "C" with the email in column "B".
Try this revised play Assume data in cols A, B, C (as per post) start in row2 down In D2: =IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($ C2,$B:$B,0)),"") Copy D2 to E2, fill down to last row of data in col C. This should return cols A & B aligned with the bounced email in col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Thanx Max for your help, but it isnt working how I intended it to. I wanted to be able to match up the Bounced emails in Column "C" with the emails in column "B". Your formula has marked the bounced emails with an "x" in column "D" and when sorting hasnt moved them next to the duplicate email in Column "B". Is there something im doing wrong or is the formula incorrect? Sorry for the inconvience and thanx for your help in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To Max
May I see the coding since I m not familiar with Macro..
Thanks in advance.. Regards, nway.gandar demechani wrote: Try this revised playAssume data in cols A, B, C (as per post) start in row2 23-May-08 Try this revised play Assume data in cols A, B, C (as per post) start in row2 down In D2: =IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($ C2,$B:$B,0)),"") Copy D2 to E2, fill down to last row of data in col C. This should return cols A & B aligned with the bounced email in col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Previous Posts In This Thread: On Thursday, May 22, 2008 12:25 PM AGOLF wrote: Compare 2 columns and align duplicates into same row Hi Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike On Friday, May 23, 2008 8:39 AM demechani wrote: Try this play .. Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Friday, May 23, 2008 11:00 AM AGOLF wrote: Thanx Max for your help, but it isnt working how I intended it to. Thanx Max for your help, but it isnt working how I intended it to. I wanted to be able to match up the Bounced emails in Column "C" with the emails in column "B". Your formula has marked the bounced emails with an "x" in column "D" and when sorting hasnt moved them next to the duplicate email in Column "B". Is there something im doing wrong or is the formula incorrect? Sorry for the inconvience and thanx for your help in advance. "Max" wrote: On Friday, May 23, 2008 11:16 AM demechani wrote: Try this revised playAssume data in cols A, B, C (as per post) start in row2 Try this revised play Assume data in cols A, B, C (as per post) start in row2 down In D2: =IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($ C2,$B:$B,0)),"") Copy D2 to E2, fill down to last row of data in col C. This should return cols A & B aligned with the bounced email in col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Monday, December 22, 2008 7:57 PM bob sacco wrote: EXCEL: Matching then Aligning Data across rows and down columns Hi All, I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out. Each month, I get a list of the top 150 advertisers and the dollar amount spent on 8 different online sites. Many of the top advertisers across these 8 online sites are duplicated but not all. So, what I end up with is several list of sites with corresponding dollars but it's not organized as a whole. My goal is to MATCH and ALIGN the data across rows and down columns so that EACH ROW has the SAME advertiser and their corresponding dollars spent. Example: ......Column A....Column B....Column C....Column D ROW1: Expedia....$3,200 ........................ ROW2: ....................... Expedia ....$5,300 Desired result: ......Column A....Column B....Column C.....Column D ROW 1: Expedia ..$3,200........Expedia ...$ 5,300 Any help would be much appreciated. Thanks bob On Monday, November 09, 2009 2:30 AM nway.gandar wrote: coding May I see the coding since I am not familiar with Macro.. Thanks in advance.. Regards, nway.gandar EggHeadCafe - Software Developer Portal of Choice WebClient Class: Gotchas and Basics http://www.eggheadcafe.com/tutorials...s-gotchas.aspx |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To Max
May I see the coding since I m not familiar with Macro..
But there's no code/vba involved. Its a formulas play Re-read the thread. All the set-up details are given in my responses -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
coding
May I see the coding since I am not familiar with Macro..
Thanks in advance.. Regards, nway.gandar demechani wrote: Try this play .. 23-May-08 Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Previous Posts In This Thread: On Thursday, May 22, 2008 12:25 PM AGOLF wrote: Compare 2 columns and align duplicates into same row Hi Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike On Friday, May 23, 2008 8:39 AM demechani wrote: Try this play .. Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Friday, May 23, 2008 11:00 AM AGOLF wrote: Thanx Max for your help, but it isnt working how I intended it to. Thanx Max for your help, but it isnt working how I intended it to. I wanted to be able to match up the Bounced emails in Column "C" with the emails in column "B". Your formula has marked the bounced emails with an "x" in column "D" and when sorting hasnt moved them next to the duplicate email in Column "B". Is there something im doing wrong or is the formula incorrect? Sorry for the inconvience and thanx for your help in advance. "Max" wrote: On Friday, May 23, 2008 11:16 AM demechani wrote: Try this revised playAssume data in cols A, B, C (as per post) start in row2 Try this revised play Assume data in cols A, B, C (as per post) start in row2 down In D2: =IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($ C2,$B:$B,0)),"") Copy D2 to E2, fill down to last row of data in col C. This should return cols A & B aligned with the bounced email in col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Monday, December 22, 2008 7:57 PM bob sacco wrote: EXCEL: Matching then Aligning Data across rows and down columns Hi All, I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out. Each month, I get a list of the top 150 advertisers and the dollar amount spent on 8 different online sites. Many of the top advertisers across these 8 online sites are duplicated but not all. So, what I end up with is several list of sites with corresponding dollars but it's not organized as a whole. My goal is to MATCH and ALIGN the data across rows and down columns so that EACH ROW has the SAME advertiser and their corresponding dollars spent. Example: ......Column A....Column B....Column C....Column D ROW1: Expedia....$3,200 ........................ ROW2: ....................... Expedia ....$5,300 Desired result: ......Column A....Column B....Column C.....Column D ROW 1: Expedia ..$3,200........Expedia ...$ 5,300 Any help would be much appreciated. Thanks bob EggHeadCafe - Software Developer Portal of Choice How to hold a successful meeting http://www.eggheadcafe.com/tutorials...uccessful.aspx |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
coding
Coding for what? Otto
"nway.gandar" wrote in message ... May I see the coding since I am not familiar with Macro.. Thanks in advance.. Regards, nway.gandar demechani wrote: Try this play .. 23-May-08 Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: Previous Posts In This Thread: On Thursday, May 22, 2008 12:25 PM AGOLF wrote: Compare 2 columns and align duplicates into same row Hi Im trying to sort out my email list. After doing a mail out to the list of 1500 I found that about 220 had bounced. I am now faced with trying to link up the bounced emails to the persons they belong to. I have 3 columns Fname/Lname column A, Email Column B, Bounced Emails Column C. As columns A & B both are the same size (1500 rows) it's making finding the 220 in column C very time consuming. I am hoping that I can find a formula or something that will let me find the duplicates in column C and sort them to the same rows in column B rather than just highlighting the duplicates which I have been able to do. But the sorting to the correct row is proving to be very difficult. Hope this is clear to you all what im looking for? Thanx for your help in advance, Mike On Friday, May 23, 2008 8:39 AM demechani wrote: Try this play .. Try this play .. Assume data in cols A, B, C (as per post) start in row2 down Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","") Copy D2 down to the last row of data in col B. This flags email in col B which match those in col C with an "x". Apply autofilter on col D, choose "x", this gives you the filtered results (in cols A and B) that I think you're after. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Friday, May 23, 2008 11:00 AM AGOLF wrote: Thanx Max for your help, but it isnt working how I intended it to. Thanx Max for your help, but it isnt working how I intended it to. I wanted to be able to match up the Bounced emails in Column "C" with the emails in column "B". Your formula has marked the bounced emails with an "x" in column "D" and when sorting hasnt moved them next to the duplicate email in Column "B". Is there something im doing wrong or is the formula incorrect? Sorry for the inconvience and thanx for your help in advance. "Max" wrote: On Friday, May 23, 2008 11:16 AM demechani wrote: Try this revised playAssume data in cols A, B, C (as per post) start in row2 Try this revised play Assume data in cols A, B, C (as per post) start in row2 down In D2: =IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($ C2,$B:$B,0)),"") Copy D2 to E2, fill down to last row of data in col C. This should return cols A & B aligned with the bounced email in col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AGOLFA" wrote: On Monday, December 22, 2008 7:57 PM bob sacco wrote: EXCEL: Matching then Aligning Data across rows and down columns Hi All, I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out. Each month, I get a list of the top 150 advertisers and the dollar amount spent on 8 different online sites. Many of the top advertisers across these 8 online sites are duplicated but not all. So, what I end up with is several list of sites with corresponding dollars but it's not organized as a whole. My goal is to MATCH and ALIGN the data across rows and down columns so that EACH ROW has the SAME advertiser and their corresponding dollars spent. Example: .....Column A....Column B....Column C....Column D ROW1: Expedia....$3,200 ........................ ROW2: ....................... Expedia ....$5,300 Desired result: .....Column A....Column B....Column C.....Column D ROW 1: Expedia ..$3,200........Expedia ...$ 5,300 Any help would be much appreciated. Thanks bob EggHeadCafe - Software Developer Portal of Choice How to hold a successful meeting http://www.eggheadcafe.com/tutorials...uccessful.aspx |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
EXCEL: Matching then Aligning Data across rows and downcolumns
Hi All,
I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out. Each month, I get a list of the top 150 advertisers and the dollar amount spent on 8 different online sites. Many of the top advertisers across these 8 online sites are duplicated but not all. So, what I end up with is several list of sites with corresponding dollars but it's not organized as a whole. My goal is to MATCH and ALIGN the data across rows and down columns so that EACH ROW has the SAME advertiser and their corresponding dollars spent. Example: ......Column A....Column B....Column C....Column D ROW1: Expedia....$3,200 ........................ ROW2: ....................... Expedia ....$5,300 Desired result: ......Column A....Column B....Column C.....Column D ROW 1: Expedia ..$3,200........Expedia ...$ 5,300 Any help would be much appreciated. Thanks bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare columns remove duplicates | Excel Discussion (Misc queries) | |||
| How to compare two columns and remove duplicates? | Excel Discussion (Misc queries) | |||
| how do i compare two columns and remove duplicates? | New Users to Excel | |||
| compare two columns and remove duplicates | Excel Worksheet Functions | |||
| Macro to align and compare multiple rows and columns | New Users to Excel | |||
Hybrid Mode
