I must admit I don’t know how to write macros in Excel so I wonder if someone can help.
I have the dataset that looks something like that:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
> </o>
Column A Column B Column C ...... Column X<o></o>
Investment 1 Investor 1, Investor 2, etc Investor 1 Investor n
Columns C to X are a result of splitting a chunk of text in Column B into separate columns. So columns D to X may or may not have text values (depending whether there has been more than one investor).
I need the data set to be transformed into the following:
Investment 1 Investor 1
New rows:
Investment 1 Investor 2
Investment 1 Investor 3
I guess essentially I need to insert as many rows after the existing row as there are non-empty cells in columns D to X. I would like the information from the existing row to be copied into those cells and ideally, shifted to the left by one cell (i.e. in each subsequent inserted row the value in the Column C would mean another investor (first inserted row - data from Column D of the original row (investor 2), second inserted row - data from column E of the original row i.e. investor 3, etc.)
I would need to do this procedure on multiple worksheets (90+) and the number of existing rows is in the hundreds in each sheet so I cant really do it manually.
The problem is I would not know how to go at it as I can’t write my own macros and I did not find anything easily modifiable to suit my purpose online either. Any help would be appreciated.
I have the dataset that looks something like that:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o> </o>Column A Column B Column C ...... Column X<o
></o>Investment 1 Investor 1, Investor 2, etc Investor 1 Investor n
Columns C to X are a result of splitting a chunk of text in Column B into separate columns. So columns D to X may or may not have text values (depending whether there has been more than one investor).
I need the data set to be transformed into the following:
Investment 1 Investor 1
New rows:
Investment 1 Investor 2
Investment 1 Investor 3
I guess essentially I need to insert as many rows after the existing row as there are non-empty cells in columns D to X. I would like the information from the existing row to be copied into those cells and ideally, shifted to the left by one cell (i.e. in each subsequent inserted row the value in the Column C would mean another investor (first inserted row - data from Column D of the original row (investor 2), second inserted row - data from column E of the original row i.e. investor 3, etc.)
I would need to do this procedure on multiple worksheets (90+) and the number of existing rows is in the hundreds in each sheet so I cant really do it manually.
The problem is I would not know how to go at it as I can’t write my own macros and I did not find anything easily modifiable to suit my purpose online either. Any help would be appreciated.
