Not just remove dup rows but combine columns

I am trying to combine multiple contact lists - I want to not only get rid of 
the dups, but unfortunately some duplicated rows have data in columns that 
other rows don't. See below. 

Last	First	Phone	Phone 2	Email
Johnson	David	917		
Smith	Jim	917		
Johnson			212	dav@

I'd love to find some way that the above example would become:

Last	First	Phone	Phone 2	Email
Johnson	David	917	212	dav@
Smith	Jim	917		

Any help would be incredibly appreciated
0
Utf
1/13/2010 6:18:04 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
799 Views

Similar Articles

[PageSpeed] 58

Hi,

The obvious question here is how does one know that Johnson and Johnson 
David are the same

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Colin" <Colin@discussions.microsoft.com> wrote in message 
news:0B0EE125-CB15-4D7C-A3B1-1BC5071F2047@microsoft.com...
> I am trying to combine multiple contact lists - I want to not only get rid 
> of
> the dups, but unfortunately some duplicated rows have data in columns that
> other rows don't. See below.
>
> Last First Phone Phone 2 Email
> Johnson David 917
> Smith Jim 917
> Johnson 212 dav@
>
> I'd love to find some way that the above example would become:
>
> Last First Phone Phone 2 Email
> Johnson David 917 212 dav@
> Smith Jim 917
>
> Any help would be incredibly appreciated 

0
Ashish
1/14/2010 3:56:25 AM
Reply:

Similar Artilces:

remove macros, ActiveX controls, XML expansion pack info, or web c
why do I get the following message in excel: "This document contains macros, ActiveX controls, XML expansion pack information, or web components. These may include personal information that cannot be removed by the Document Inspector." ...is there a way to remove the "macros, ActiveX controls, XML expansion pack information, or web components"? I do have the trust center "remove personal information from file properties on save" checked, but have not created macros, and am not sure what the ActiveX or XML info could be, unless it was somehow pasted ...

Code 17 Dup Record on file FA_Retirement_Mstr
V. 7.00g using the FA module only (not interfacing with GL) Retired 600+ assets. Un-retired to correct proceeds. Re-retired and the first 390 went fine, now every asset we try to re-retire gives us this error. What can we do? Thank you, Robert ...

Search Desktop Removal
I installed office 2007 and am not happy with Search Desktop - how do I remove this? I looked in Remove and Add Programs but it is not there. Any suggestions are apprecaited. That's Windows Desktop Search. What don't you like about it? Its definitely faster than the native outlook search. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLE...

Hiding columns and custom views problem
Hello: I have defined 6 views in modules that define views. The views are simply hiding or showing columns based on a value in a hidden row. These columns are also grouped in outlines. I am having issues with one of the views which is spitting out the "can't move objects off sheet" error. I already have code in the workbook that autosets the "size with cells" property of inserted comments. Are there some caveats with hiding/showing columns which are grouped? Are there limits to the number of columns that can be manipulated in this way? My sheet has 150 colum...

Remove sort and filters programmatically on form
I have a form based on a 2 table query. User will use shortcut menu to filter records down to what they want to work on and sort accordingly. I have a button to remove all filters but I also want to remove any sorts that were applied and I can't seem to get that part to work. I use the following to remove all the filters. I just need help with the code to get the form back to the original underlying query sort. On_Click event of button control Me.Filter = "" FilterOn = False Like I said, this brings back all records but the sort stays. I am using Access 200...

insert blank row every 10 rows
Is it possible to insert a blank row every 10th row starting from row 11 and going until the last and variable row of data? maybe this? Sub test() Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row Do Until lastrow < 11 If lastrow Mod 10 = 0 Then ws.Rows(lastrow).Offset(-9).EntireRow.Insert End If lastrow = lastrow - 1 Loop End Sub -- Gary Keramidas Excel 2003 "aileen" &...

Unable to unhide some hidden rows
I have been using Excel 2002 to track events. There are about 950 rows and 15 columns. As events are completed, I hide the rows so that I can see only active events. However, I occasionally need to unhide a row in order to see some data. Then I rehide it. When I say "I", I set it up but a receptionist has been using it. I've found lots of mistakes in her entries and she no longer works for me. A few days ago, I tried unhiding all rows, double checking data. I discovered three rows that I cannot unhide - row 776, 790, and 807. I closed the program and a couple of...

can you hide rows with a formula?
on my last sheet there should be an overview of the data selected by the user. If the user doesn’t select all the blocks with data “because he thus not need that particular block”. There will be empty rows between the information on the last sheet! I need all the info under each other, so that the user will have a short clear overview that will fit on 1 page to print. (this should be all automatic). Is this possible by hiding the empty rows with a formula? Or is there another way? (The sow called “empty rows” do contain formulas that hide the data blocks that the user thus not need)...

Combining two columns into one- Excel 2007
I have two columns of information. The first contains the date and the second contains a number. I want to combine these two into a single column where the date is the first piece of data and the number is the second, etc. etc. I have about 15000 sets of data (date and number), so to do this manually would be out of the question. Any suggestions for a "novice" programmer? Thanks Steve Clarification What I want to do is insert a blank row every other row, and then move the data in column b1 down into A2, b2 into A4 etc. Original worksheet A B 2/1/2007 45 2/2/2007 49 2/3/2007...

Combinations...
Below is an algorithm by by Donald Knuth to generate possible combinations. Can this be translated to VBA? Or, does exist a similar procedure in VBA? /* Algorithm by Donald Knuth. */ #include <stdio.h> #include <stdlib.h> void main( void) { int i, j=1, k, n, *c, x; printf( "Enter n,k: "); scanf( "%d,%d", &n, &k); c = malloc( (k+3) * sizeof(int)); for (i=1; i <= k; i++) c[i] = i; c[k+1] = n+1; c[k+2] = 0; j = k; visit: for (i=k; i >= 1; i--) printf( "%3d", c[i]); printf( "\n"); if (j > 0) {x = j+1; goto incr;...

Inserting blank rows plus using Sum
I have a list of stocks sorted by date. When the stock symbol changes, I need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500...

Finding values in different rows/columns
This should be simple, but I'm lost. To greatly simplify my sheets: 1 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 2 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 I want to do a VLOOKUP(1,A1:A10,1,false) and return the value in D4, or VLOOKUP(2,A1:A10,1,false) and return the value in C9. In other words, find my A cell reference and return the value in a different row/column from that. Another question: When I move a cell (cell1) to another cell (cell2) a cell (cell3) that references cell1 moves its reference from cell1 to c...

showing repeating values in the rows of a pivot table
The default setting in pivot tables results in showing the repeating value of a row header in the first row only. Is it possible to allow the repeating value to populate all the cells to which it applies? Not while it's still a pivottable. I'll usually convert to values (or make a copy of that pivottable and convert that copy to values). Then I'll use techniques like those at Debra Dalgleish's site to fill those empty cells: http://www.contextures.com/xlDataEntry02.html kdschreck wrote: > > The default setting in pivot tables results in showing the repeating valu...

Excel's COMBIN and integers
In theory COMBIN works with integers in Excel (2003 and 2007). So "=COMBIN(9,3)" gives 84 and so does "=COMBIN(9.7,3.6)". Similarly "=COMBIN(9,3)-84" gives 0. But "=COMBIN(9,3)-84-0" gives -1.42109E-14 and there are many similar examples suggesting that some sort of rounding is involved. The sign can even change so for example "=COMBIN(15,3)-455-0" gives 5.68434E-14 =IF(COMBIN(9,3)=84,"same","different") and =IF(COMBIN(9,3)-84=0,"same","different") do not give identical results. Nor do ...

Big prodject! How to insert row or rows beneath certain text
Is there a code that when a certain text is in collumn (A3) ALL CORE i insert one row beneath it, Also if there is one or more text next t each other, Like Collumn (A9 and A10) HUNTER-#2 and HUNTER-#2 an there're the exactly the same then insert two rows beneath the las Hunter-#2. But the thing is I only want it to happen to specifics text and can it have exceptions like at A5 and A6 the text reads COPE & DRA #2 and under it says COPE & DRAG #3 can the code it insert two row beneath COPE & Drag #3 but if COPE & DRAGE #3 does not appear the insert one row beneath COPE &...

Just upgraded test 3.0 install to 4.0, weird error when opening account?
We are working on a full implementation to crm 3.0 in our office. Still working out some of the bug there. So, we decided to upgrade our test box to 4.0, so we can see if any of the bug we are having are worked out in 4.0. It appears that maybe they are... YAY :) But, when i open an account in 4.0, i get an error box that i have to hit OK 4 times, then everything is alright, and i can see the account, and check everything out. here is the error that is in the bottom of the pop up: http://127.0.0.1:5555/companyname/_common/error/dlg_error.aspx?hresult=Not%20available&errmessage= Tha...

Removing a control from the Controls Toolbox
How do I remove a custom control from the controls toolbox in VS 6? ...

Easy way to remove access to a menu item and all submenus?
I'm still trying to get the hang of using Advanced Security, and I don't know if I'm doing things the hard way or not. For example, I installed SmartList Builder on several user's PCs so they could run some custom SmartLists that I developed. However, I don't want them to be able to have access to the menu items that appear under Tools>SmartList Builder. Is there a fast way of shutting off the entire menu option (and all sub-menus) for SmartList Builder? The only way I can figure out how to do it is to go through each menu option, one by one, and eliminate access...

mail removed from control panel
Hello, My mail shortcut is removed from my control panel. I did a search, I must have deleted it. How do I create a new one? Boe <boe_d@notspam.hotmail.com> wrote: > My mail shortcut is removed from my control panel. I did a search, I > must have deleted it. How do I create a new one? Perhaps a Help>Detect and Repair in Outlook. -- Brian Tillman Boe, you wrote on Tue, 21 Feb 2006 07:06:15 -0800: > My mail shortcut is removed from my control panel. I did a search, I must > have deleted it. How do I create a new one? May it be that you mean "Desktop...

vba macro to perform custom reporting -transpose from rows to columns
I have one workbook and two sheets 1 sheet has data for automated test scenarios that may be run up to 3 time if they don't pass on the first or second try Sheet1: TestSet Test Result reason if failed 1 pass 2 pass 3 pass 4 fail x 4 fail y 4 fail z I want to create a summary report that looks like Sheet2 for the Sheet1 logs above test run1 result test run2 result test run 3result Sheet2 Testset result1 result2 result3 1 pass 2 pass 3 pass 4 fail fail fail So if a testset is executed more ...

SmartList columns
Is there a way to get the Checkbook Description to show up on the smartlist feature? I am trying to create a positive pay file smartlist to send to our bank and we have our Bank's account number in that field which they need for the file. Any suggestions? Reid, if you have smartlist builder then utilize table, CM00100, to get the checkbook description. Thanks, rc "Reid Peters" wrote: > Is there a way to get the Checkbook Description to show up on the smartlist > feature? I am trying to create a positive pay file smartlist to send to our > bank and we have ou...

SUM 2 COLumns values with common names in another columns
> hi > how can i sum 2 columns from 2 tables having in common the same names > by side of the numbers to sum? hi how can i sum 2 columns from 2 tables having in common the same names by side of the numbers to sum? table1 column1 column2 1 a 2 b 3 c 4 d 5 e 6 f 7 g 8 h 9 i table2 col1 col2 1 b 2 d 3 a 4 f 5 c 6 f 7 g 8 h table3 col1 ...

duplicate data in column
I created a spreadsheet with three columns from data pulled that may not be correct. One of the columns is MAC address which I know there are duplicates in this column. Based off the 500 rows, I am trying to figure how can I find out where a MAC Address is in the MAC Address more than once. I have found that the MAC address which is tied to a device could be in two locations and I am trying to narrow where it might be since the place we get all the data is never updated. Excel Format example of the file Location Number MACAddress DC1 21 00A0F8610E1C DC1 20 00A0F8610E...

using writexml in vb .net and numeric columns in excel
I use the following writexml method to convert a dataset/datatable to .xml: ds.WriteXml(mstrpath, XmlWriteMode.WriteSchema) ds.WriteXmlSchema(mstrpathxsd) The path strings are simple full paths: eg, f:\emapps\xmlfiles\filename.xml (or .xsd as appropriate). I get an xml file, but there are 2 problems: 1. text that looks like numbers - eg, a column with 45622, 00987, 01234, etc - drops the prefix '0' and is formatted inside excel as numeric columns. This is so inside excel and in the .xml file itself. 2. all numeric columns - whether they should be numerics or not - have an ex...

Multiple selections of rows & columns by clicking in one cell
Don't know what is going on with my point & click but it selects multiple rows & columns vs just one cell. Now I can't enter data, because it may select anywhere from 4 to 10 rows/columns, don't know why it started doing this....now I was entering data and held the SHIFT key down, and the accessibility feature popped up and I (thought) I clicked "cancel" . I went back and checked the settings, they are the same, don't know what to do and of course I have a huge report due .... ...