SOS Help Needed ASAP

[mple_elephantes]

Dear all,

I have six different spreadsheet, each one having a total of 8 (A-H) columns (and various rows). The data have to with patents, and what I need is the following: some patents appear more that one times but each row has different values. What I need is to merge the duplicates but keep the unique values. I have also attached an example. I have come to realise that I need some sort of a code, but I really dont know how to do it. CAn someone please, please help me!!

[TABLE="width: 894"]
<tbody>[TR]
[TD]Turn this:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]appln_id[/TD]
[TD]appln_filing_year[/TD]
[TD]appln_auth[/TD]
[TD]ipc_class_symbol[/TD]
[TD]person_ctry_code[/TD]
[TD]sector[/TD]
[TD]applt_seq_nr[/TD]
[TD]invt_seq_nr[/TD]
[/TR]
[TR]
[TD="align: right"]21487773[/TD]
[TD="align: right"]2005[/TD]
[TD]GB[/TD]
[TD]H01L 31/042[/TD]
[TD]JP[/TD]
[TD]COMPANY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]21487773[/TD]
[TD="align: right"]2005[/TD]
[TD]GB[/TD]
[TD]H01L 31/042[/TD]
[TD]GB[/TD]
[TD]INDIVIDUAL[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21487773[/TD]
[TD="align: right"]2005[/TD]
[TD]GB[/TD]
[TD]H01L 31/042[/TD]
[TD]GB[/TD]
[TD]UNIVERSITY[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Into this:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]appln_id[/TD]
[TD]appln_filing_year[/TD]
[TD]appln_auth[/TD]
[TD]ipc_class_symbol[/TD]
[TD]person_ctry_code[/TD]
[TD]sector[/TD]
[TD]applt_seq_nr[/TD]
[TD]invt_seq_nr[/TD]
[/TR]
[TR]
[TD="align: right"]21487773[/TD]
[TD="align: right"]2005[/TD]
[TD]GB[/TD]
[TD]H01L 31/042[/TD]
[TD]GB/JP[/TD]
[TD]COMPANY/INDIVIDUAL/UNIVERSITY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

is this a one off or something you will have to do often

if its one off

I1 = A1&B1&C1&D1&E1&F1&G1&H1

Drag down

then select I and delete duplicates

filter on empty I rows and delete the associated ones, then you can delete I

one list no duplicates

 

[mple_elephantes]

Hey mole999,

Thanks for the reply. However, this would not do. I need them to be on different columns - as in the example above. I think it is only possible using code, but I don't know hot to do it. Also the rows go up to 3000-4000 and I have 6 such tables :/

 

[mole999]

if you merge data how will your code know which to accept as the final answer

 

[mple_elephantes]

I don't know... It could identify the duplicates from Column A, and merge everything else..?!

 

[hiker95]

mple_elephants,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Following your example text, should the results be displayed as the YELLOW cells?

Excel 2007
	A	B	C	D	E	F	G	H
1	appln_id	appln_ filing_ year	appln_ auth	ipc_ class_ symbol	person_ ctry_ code	sector	applt_ seq_ nr	invt_ seq_ nr
2	21487773	2005	GB	H01L 31/042	JP	COMPANY	1	0
3	21487773	2005	GB	H01L 31/042	GB	INDIVIDUAL	0	1
4	21487773	2005	GB	H01L 31/042	GB	UNIVERSITY	0	2
5
6	Into this:
7	appln_id	appln_ filing_ year	appln_ auth	ipc_ class_ symbol	person_ ctry_ code	sector	applt_ seq_ nr	invt_ seq_ nr
8	21487773	2005	GB	H01L 31/042	GB/JP	COMPANY/INDIVIDUAL/UNIVERSITY	1	1
9
10
11	appln_id	appln_ filing_ year	appln_ auth	ipc_ class_ symbol	person_ ctry_ code	sector	applt_ seq_ nr	invt_ seq_ nr
12	21487773	2005	GB	H01L 31/042	JP/GB	COMPANY/INDIVIDUAL/UNIVERSITY	1/0	0/1/2
13
Sheet1

I have adjusted the cells with the titles to fit in the MrExcel display area.

 

[hiker95]

mple_elephants,

I have six different spreadsheet, each one having a total of 8 (A-H) columns (and various rows).

1. Do you have six workbooks?

2. Or, do you have one workbook with six worksheets?

 

[mple_elephantes]

Dear mole999 and hiker95,

thank you very much for your answers and willing to help, i really appreciate it.

i have now resolved the issue with an excel add-in named "combine rows excel", which really did what I wanted.

Once again, thank you both

 

[hiker95]

mple_elephants,

Thanks for the feedback.

You are very welcome. Glad we could help.

Glad that you were able to find an answer that worked for you.

And, come back anytime.