Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining information from rows
I have an excel spreadsheet with lots of duplicate contacts, but their
information is spread throughout the different rows. For example, I have 3 rows for John Doe, but his address is in the first row, his phone number is in the second row, and his affiliation is in the third row. Is there a way to merge the information from all three rows into one? I have a few thousand records like this. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining information from rows
Question: are all your records like:
John Doe 1 A street Jane Doe 3 B str John Doe 212-555-5555 Mike Doe 7 C str John Doe affil1 Jane Doe 313-444-4444 Mike Doe 414-666-6666 Jane Doe affil2 Mike Doe affil3 In other words, (1) do you always have the key in first column? (2) is phone always in a later row than address and affiliation in a later row than phone? On Jul 2, 7:43 pm, Danielle wrote: I have an excel spreadsheet with lots of duplicate contacts, but their information is spread throughout the different rows. For example, I have 3 rows for John Doe, but his address is in the first row, his phone number is in the second row, and his affiliation is in the third row. Is there a way to merge the information from all three rows into one? I have a few thousand records like this. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining information from rows
The records are like this:
First Last Phone Address Affiliation John Doe 555-555-5555 John Doe 123 Road John Doe Yes Jane Doe 555-555-5555 Etc. Yes, the phone is always in a later row than address, and affiliation is in a later row than that. "vezerid" wrote: Question: are all your records like: John Doe 1 A street Jane Doe 3 B str John Doe 212-555-5555 Mike Doe 7 C str John Doe affil1 Jane Doe 313-444-4444 Mike Doe 414-666-6666 Jane Doe affil2 Mike Doe affil3 In other words, (1) do you always have the key in first column? (2) is phone always in a later row than address and affiliation in a later row than phone? On Jul 2, 7:43 pm, Danielle wrote: I have an excel spreadsheet with lots of duplicate contacts, but their information is spread throughout the different rows. For example, I have 3 rows for John Doe, but his address is in the first row, his phone number is in the second row, and his affiliation is in the third row. Is there a way to merge the information from all three rows into one? I have a few thousand records like this. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining information from rows
First you need to produce a list of First/Last without duplicates. You
can use the advanced filter for this. 1. Insert an empty column after column B, to separate the first two columns. 2. Click in A2. Issue DataFilterAdvanced filter... 3. Filter the list in another location. Check H1. Check also Unique records only. At the end of this step you have just the names without duplicates. Complete the headers next to First and Last. Now use the following *array* formula in J2 (array formula: commit with Shift+Ctrl+Enter): =INDEX(C$2:C$7,MATCH(1,($A$2:$A$7=$H2)*($B$2:$B$7= $I2)*(C$2:C$7<""), 0)) You can copy the formula to the right and down for as many rows as necessary. In my example I assumed data go up to row 7, which they don't. Change the 7 to the last row with data. Leave the $$ as they are. HTH Kostis On Jul 2, 9:37 pm, Danielle wrote: The records are like this: First Last Phone Address Affiliation John Doe 555-555-5555 John Doe 123 Road John Doe Yes Jane Doe 555-555-5555 Etc. Yes, the phone is always in a later row than address, and affiliation is in a later row than that. "vezerid" wrote: Question: are all your records like: John Doe 1 A street Jane Doe 3 B str John Doe 212-555-5555 Mike Doe 7 C str John Doe affil1 Jane Doe 313-444-4444 Mike Doe 414-666-6666 Jane Doe affil2 Mike Doe affil3 In other words, (1) do you always have the key in first column? (2) is phone always in a later row than address and affiliation in a later row than phone? On Jul 2, 7:43 pm, Danielle wrote: I have an excel spreadsheet with lots of duplicate contacts, but their information is spread throughout the different rows. For example, I have 3 rows for John Doe, but his address is in the first row, his phone number is in the second row, and his affiliation is in the third row. Is there a way to merge the information from all three rows into one? I have a few thousand records like this. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining information from 2 Spreadsheets into 1 | Excel Discussion (Misc queries) | |||
Combining Information from rows | Excel Discussion (Misc queries) | |||
Help with combining information | Excel Discussion (Misc queries) | |||
combining row information | Excel Discussion (Misc queries) | |||
Combining Information in a Bar Chart | Charts and Charting in Excel |