How to convert these rows into columns?

[elbrujo]

Hello there,

Thank you for your time.
I have a table like this:

3	[Pinzon, M. J.; Alfonso, J. E.; Olaya, J. J.] Univ Nacl Colombia, Grp Ciencia Mat & Superficies, Bogota 14490, Colombia
4	[Escorcia, Andres M.; Daza, Martha C.; Doerr, Markus] Univ Ind Santander, Grp Bioquim Teor, Bucaramanga, Colombia; [Molina, Daniel] Univ Ind Santander, Lab Resonancia Magnet Nucl, Piedecuesta, Colombia; [Doerr, Markus] Univ Santo Tomas, Fac Quim Ambiental, Bucaramanga, Colombia
5	[Camacho, Guillermo A.] La Salle Univ, Fac Engn, Bogota, Colombia; [Llanos, Carlos H.] Univ Brasilia, Dept Mech Engn, Brasilia, DF, Brazil; [Berger, Pedro A.] Univ Brasilia, Dept Comp Sci, Brasilia, DF, Brazil; [Miosso, Cristiano Jacques; Rocha, Adson F.] Univ Brasilia, Fac Gama, Brasilia, DF, Brazil
6	[Delgado, Daniel R.; Martinez, Fleming] Univ Nacl Colombia, Dept Farm, Grp Invest Farmaceut Fis Quim, Bogota 14490, DC, Colombia

And so on, there are thousands of records just like those.

I need to divide both columns following certain pattern, and I've tried certain functions from another software, but I'm unable to achieve what I want, and I'm not sure if this can be done, *at least for certain records (I'll explain in a moment)

What I need is to isolate each author (a person's name) into its own record, containing it's own ID (the first column number), its Institution (Univ, Dept, Grp....), and the Country.

*Now, since I'm not sure if this can be done for all records, I give priority to those in Colombia, so at least the Colombian authors.

An example:

6	Delgado, Daniel R	Univ Nacl Colombia, Dept Farm, Grp Invest Farmaceut Fis Quim	Bogota 14490	DC	Colombia
6	Martinez, Fleming	Univ Nacl Colombia, Dept Farm, Grp Invest Farmaceut Fis Quim	Bogota 14490	DC	Colombia

See the records in bold?, I need priority on those, the other ones aren't that important, and they might be even difficult, so I don't know... But at least the country and the person's name

Any advise? I've attached a demo document with the first 20 rows...

Thanks for your time,
Regards.

[GC Excel]

Holà,

First, insert a new sheet in your workbook AFTER the sheet containing the data.
(Sheet 1 with data, Sheet 2 to ouput result)

Then, run this first macro :

Please Login or Register  to view this content.

Followed by this one :

Please Login or Register  to view this content.

The first macro will split the data for each author found
The second macro will put the country in the last column

[elbrujo]

Hello,

Thank you very much for your help!, it worked just fine,

However the macro stops working since it finds data that doesn't qualify the criteria, for example rows 32, 48 and 49.. I think.

Any advise?

Thank you very much for your time

[GC Excel]

Hi,

Try changing the first macro with this :

Please Login or Register  to view this content.

[elbrujo]

Hello there,

Thank you very much for your help,
It worked just fine, thank you.

Unfortunately it is ending unexpectedly at row: 1863 (or ID 1900)
Do you know why would this happen?

There are some empty rows where it failed, where I simply added an "empty" word so it would work, but the issue at row 1863 (or ID 1900) I'm not sure what could be causing it,

Any advise?

I had to attach it to dropbox since it is bigger than 1 MB (1.2 MB)
https://www.dropbox.com/s/oy1qvfpll4...ook4.xlsx?dl=0

I'd highly appreciate your help!

[GC Excel]

Hello again,

Problem was caused because there was no [] for the first "authors" (before [Arredondo A.M.)
I modified the macro a bit to take care of the empty rows and the probem at row 1863.
However it is not perfect as you can havo other different combinations (examples, list of authors only with no ending ] and no address (see ID 2004).
I've added an "on error resume next" instruction in the macro. This will prevent the macro from stopping but you may end up with weirds results in some cases.

See ID 1869, 1966 , 2004 ,2095 for example.

I'd say that 98% of the cases will be ok.

I won't have more time to work on this today. Just let me know if you can work with this for now.

Please Login or Register  to view this content.