Please help with coping cells

[Guest]

I have the following sample of data in an excel sheet. This lay out is
repeated for couple hundred rows in exactly the same fashion.
********************************************************
Building Primate Clinic CRV($000's) $603 Building Number 504 GSF 2,455

Subsystem Backlog 2008 2009 2010 2011 2012
j.1. Fire Detection Systems $8 $0 $0 $0 $0 $0
l.1. Interior Finishes $0 $0 $0 $0 $0 $32
************************************************************
I need to copy the lables "Builing" and "Building Number" and paste them in
the 2 cells on the left side, next to the word "Subsystem". then copy the
values "Primate Clinic" and "504" and paste them in the cells under their
perspective lables "Builing" and "Building Number". Pasting the values should
stop before the next row that has the lables of "Builing" and "Building
Number" starts again. I am desperate. can some one help.
thanks in advance.
Al

 

[Bill Renaud]

Hi Al,

I saw your previous posts on this topic starting on 9/17/2007 ("Help with
insert a row"). I was busy working on other things, but would like to take
a look at your Excel file to see exactly how the data is arranged. I use
Excel 2000, so if you could export the worksheet to a CSV
(comma-separated-value) file (set in the Type field of the SaveAs dialog
box and send it to me, I would be interested. It shouldn't be that
difficult to do what you want.

I think the best approach would be to just convert the entire worksheet
over to a single list with one header in row 1, then all of the data below
it, like this (letters above each example are column labels):

Before:
A B C D E F G
=========================================================================
Building Primate Clinic CRV($000's) $603 Building Number 504 GSF 2,455

Subsystem Backlog 2008 2009 2010 2011 2012
j.1. Fire Detection Systems $8 $0 $0 $0 $0 $0
l.1. Interior Finishes $0 $0 $0 $0 $0 $32
=========================================================================

After:
A B C D E F G H I
=========================================================================
Building
Building Number Subsystem Backlog 2008 2009 2010 2011 2012
Primate Clinic 504 j.1. Fire Detecti.. $8 $0 $0 $0 $0 $0
Primate Clinic 504 l.1. Interior Fin.. $0 $0 $0 $0 $0 $32
=========================================================================

Copy and paste the above 2 examples in a text editor (turn word wrap off in
Notepad) with a fixed width font to line things up.

Questions:
1. How is the line "Building Primate Clinic CRV($000's) $603 Building
Number 504 GSF 2,455" arranged now? Is it already in separate cells, like
below, or is in a single cell?

Col. Value
---- ------
A Building
B Primate Clinic
C CRV($000's)
D $603
E Building Number
F 504
G GSF
H 2,455

2. Do you discard the data for CRV($000's) and GSF? Are they not needed?

 

[Guest]

HI Bill,
Thank you very much for this, I really do appreciate it. I think the best
way to answer your questions is to save the file as Excel 2000 and send it to
you so that you could see it exactly the way I have it. Since my first post I
have put together some macros to clean the data up and I was able to get it
to a much better shap but not quite there yet. I will send you a separate
file with all the macros as well. Just to tell you about the background, this
is a files that I copy from the internet as HTML and paste it in excel. which
results some merged cells and so on. I will send you a copy of the before and
a copy of the end result. It takes so much work, without the macros, to get
to the final results.
Here is the list of macro names that I run through out the process, in the
order they run:

1) DelEmptyRows---Ctrl+Shift+R
2) DelEmptyColumns ---Ctrl+Shift+C
3) Delete_Row_with_Autofilter_Array---Ctrl+Shift+A
4) Unmerge cells (Their is no macro for it yet. I just do it manuall. It is
important to do it at this point)
5) FillToleft---Ctrl+Shift+F

is this the right email to send the file to?
(e-mail address removed)
thanks
Al

 

[Bill Renaud]

Yes that is the right e-mail address. (Next time, please leave out the last
part, to try to foil spammers from "harvesting" it, or just go ahead and
send! If it's not right, you will get a bounce back, then try again. I
guess I should add "instructions" in my signature line.)

 

[Guest]

Yes, you are right. Sorry about that.
I will send it now.
Al