merging-editing lists

Mark CU · Apr 26th, 2002, 10:30 PM

If the text in columns c, d and e match then how would I get any text columns f, g, h, I, j, k, l, m, n, o, p, to be copied to both records..thank you ( very much )

Chris Davison · Apr 27th, 2002, 10:51 AM

Quote:

On 2002-04-26 21:30, Mark CU wrote:
If the text in columns c, d and e match then how would I get any text columns f, g, h, I, j, k, l, m, n, o, p, to be copied to both records..thank you ( very much )

not wanting to sound flippant, but probably very easily using =MATCH and =CONCATENATE

however, your "both" confuses me.... "both" suggests 2 of something, but I can't see what these could be....

can you be a bit more specific, I'm pretty sure the answer will be relatively simple

(also do you require macro code (VBA) to do this or were you looking for a formula-based solution ?)

Chris

Mark CU · Apr 27th, 2002, 12:26 PM

I am a newbie in advanced excel....thought I make both records the same and then delete one because ...each record has some information that I want in final record..is there a good way to do this..? thanks again

Mark CU · Apr 27th, 2002, 12:27 PM

formula bases solution would be great

Chris Davison · Apr 27th, 2002, 05:23 PM

which 2 records ?!

do you mean something like if the text in columns a and b and c are all identical, then you want another column which contains the single matching record and the contents of all columns f to p ?

try this :

=IF(EXACT(A2,B2),IF(EXACT(B2,C2),A2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2))

on row 2 somewhere, which'll match column a with B and C and if they all match, create a concatenated string of a single value from A together with everything in F to P, returning "FALSE" where there's no initial match

Mark CU · Apr 28th, 2002, 04:39 PM

Thank you,

I did try that formula, and it returned a result of false...and I think I need to be clearer on what I am trying to do..

I have a list of 5000, some of these records are duplicates of the name, address, and city. However the rest of each record of a person is different. Some of the info is needed from one of the records, and some is needed from the second record.

I want to combine the two records into one record... and there many of these duplicates .. in the list.

Thank you

Mark CU · Apr 28th, 2002, 05:33 PM

the columns that will be what I match the records on are A, B, C, D.

Columns F, G, H, I, J, K, and L are correct in one the duplicates, and the the other record contains information in columns M and N.

I would ike to merge these records into one record containing all the information..and delete the uneeded record...

Thank you for your help..

Mark CU · Apr 29th, 2002, 08:41 PM

The duplicates are in some of the rows....

so A2 : D2 would match on A2 : D3, then the rest of the information would be used would be in E2 :J2 and K3 : L3

THANK YOU

Apr 27th, 2002, 05:23 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	which 2 records ?! do you mean something like if the text in columns a and b and c are all identical, then you want another column which contains the single matching record and the contents of all columns f to p ? try this : =IF(EXACT(A2,B2),IF(EXACT(B2,C2),A2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2)) on row 2 somewhere, which'll match column a with B and C and if they all match, create a concatenated string of a single value from A together with everything in F to P, returning "FALSE" where there's no initial match __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 10:30 PM	#1
Mark CU New Member Join Date: Apr 2002 Posts: 14	If the text in columns c, d and e match then how would I get any text columns f, g, h, I, j, k, l, m, n, o, p, to be copied to both records..thank you ( very much )

Apr 27th, 2002, 12:26 PM	#3
Mark CU New Member Join Date: Apr 2002 Posts: 14	I am a newbie in advanced excel....thought I make both records the same and then delete one because ...each record has some information that I want in final record..is there a good way to do this..? thanks again

Apr 27th, 2002, 12:27 PM	#4
Mark CU New Member Join Date: Apr 2002 Posts: 14	formula bases solution would be great

Apr 28th, 2002, 04:39 PM	#6
Mark CU New Member Join Date: Apr 2002 Posts: 14	Thank you, I did try that formula, and it returned a result of false...and I think I need to be clearer on what I am trying to do.. I have a list of 5000, some of these records are duplicates of the name, address, and city. However the rest of each record of a person is different. Some of the info is needed from one of the records, and some is needed from the second record. I want to combine the two records into one record... and there many of these duplicates .. in the list. Thank you

Apr 28th, 2002, 05:33 PM	#7
Mark CU New Member Join Date: Apr 2002 Posts: 14	the columns that will be what I match the records on are A, B, C, D. Columns F, G, H, I, J, K, and L are correct in one the duplicates, and the the other record contains information in columns M and N. I would ike to merge these records into one record containing all the information..and delete the uneeded record... Thank you for your help..

Apr 29th, 2002, 08:41 PM	#8
Mark CU New Member Join Date: Apr 2002 Posts: 14	The duplicates are in some of the rows.... so A2 : D2 would match on A2 : D3, then the rest of the information would be used would be in E2 :J2 and K3 : L3 THANK YOU