Advanced Copy-Paste Question

[reerie]

This is something that's been driving me crazy. I work in a library and the following data are accession numbers for old 45 and 78 records. Here's a gross simplification of what I am currently looking at in Excel (the single quotes indicate empty cells) -

2002.400.78.00001
' '
2002.400.78.00002
' '
2002.400.78.00003
' '
2002.400.78.00004
' '
2002.400.78.00005
' '

What I'm trying to do is get the data to look like this -

2002.400.78.00001
2002.400.78.00001
2002.400.78.00002
2002.400.78.00002
2002.400.78.00003
2002.400.78.00003
2002.400.78.00004
2002.400.78.00004
2002.400.78.00005
2002.400.78.00005

To break it down more formulaically I want Excel to copy and paste a given accession number into the blank cells that follow it, until it hits the next accession number at which point I'd like the process to repeat. There are a few cases when accession numbers are separated by more than one blank cell. e.g. -

2002.400.78.00013
' '
' '
' '
2002.400.78.00014
' '
2002.400.78.00015
' '
2002.400.78.00016
' '

Which I would like to see as -

2002.400.78.00013
2002.400.78.00013
2002.400.78.00013
2002.400.78.00013
2002.400.78.00014
2002.400.78.00014
2002.400.78.00015
2002.400.78.00015
2002.400.78.00016
2002.400.78.00016

Any masters out there that could be of assistance? I'm dealing with 12000+ lines of these sorts of data. I've just about thrown the towel in.

If you need more information holler. Much thanks in advance.

 

[Hui]

Reerie

Firstly, Welcome to the Chandoo.org Forums

I would use a helper Column
Insert a Column next to your data
First copy the first accession number in A2 to B2
Then in B3: =If(A3<>"",A3,B2)
Copy this down to the end of your data

Next Select Column B
Copy
Paste as Values
Then once your happy your can delete Column A
Enjoy

 

[Somendra Misra]

Hi reerie,

If the list is static,why not use Excel in-built feature, of filter.
1. Put the auto-filter on the column with this data.
2. Than from filter drop down select everything except Blank. This will bring the list of only items.
3. Copy this filtered list and paste to desired position.

If the list is changing everyday, the task can be automated with a simple macro.

Regards,