0

I have a csv (technically, an Excel spreadsheet) that has rows/columns like this:

AC-10   ECLO-1  ECLO-2
AC-11   PESL-1  
AC-13   ABCD-2  ABDE-3  AGTU-5

What I'd like to do is create two columns so that it looks like this:

AC-10   ECLO-1  
AC-10   ECLO-2    <- Notice the 'AC-10' is now repeated with the (former) 3rd column as its value
AC-11   PESL-1
AC-13   ABCD-2  
AC-13   ABDE-3    <- Repeated row, new value from old column
AC-13   AGTU-5    <- Repeated row, same idea

Is there a relatively pain-free way to do this in Excel? I'm trying to avoid scripting out a solution.

Matt
  • 109

1 Answers1

1

NOTE: All references from memory! No Excel available, script testing not possible.

The simplest approach:

  • Click/Select cell ECLO-2
  • Find 'Record macro' - at the same place you'll find Use relative references do activate it...
  • and then Record Macro, select a shortcut key to use and then click OK to start recording.
  • Press cursor down
  • Find and use Insert Row in menus (using shortcut keys: Alt+i+r)
  • cursor up (back on top of ECLO-2 again)
  • CTRL+x
  • Home
  • cursor down
  • cursor right
  • CTRL+v
  • Home
  • cursor up
  • CTRL+c
  • cursor down
  • CTRL+v
  • cursor up
  • End
  • cursor right
  • Click Stop recording

If I typed the above correctly, and you got it right; you should now have

AC-10   [ECLO-1]        
AC-10   ECLO-2      
AC-11   PESL-1      
AC-13   ABCD-2  ABDE-3  AGTU-5

with the cursor on the ECLO-1 cell as indicated by the [brackets].

To use the just recorded shortcut key, move the cursor to AGTU-5 and then use the shortcut. That will launch the macro and place AGTU-5 on the line below in the manner you wanted. The last actions of the macro should leave the cursor on top of ABDE-3, ready for one more launch.

I hope I got it right, had I had an Excel available then it would have been tested too ;-)
Hope it gives you the idea at least.

Hannu
  • 9,289