Relocating reference numbers for many blocks of data

M

Mr. Low

Dear Sir,

I am using Seagate Infordesktop Software which generate report in MS Excel
2002 format.

My report is designed in such a way that a reference number located the to
right hand coner of each block od data.

I need to edit the data so that it appear at the first column of each line.

Original Report Format
Ref No 1
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx

Ref No 2
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx


Edited Report Format (what I need)
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx

Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx

Is there any short cut or key board command to do this instead of copy the
reference number for each block and past each blank cell one by one?

I would be glad if someone who is knowledgable in Ms Excel 2002 could assist
me.

Thanks

Low Seng Kuang
Malaysia
 
R

Ron Coderre

Try this:

With your posted report data beginning in A2

Insert a column before the data (the report now begins in B2)
A2: =IF(C2="",B2,A1)
Copy that formula down as far as you need

The report now looks like this:
Ref No 1 Ref No 1
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
0
Ref No 2 Ref No 2
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Select Col_A
<edit><copy>
<edit><paste special>
Check: Values....then click [OK]

Next, assuming Col_C blanks indicate rows to be deleted
Select Col_C
Press the [F5] key....that's the shortcut for <edit><go to>
Click the [Special] button
Check: Blanks.....then click the [OK] button
(now, all of the blank cells in Col_C are selected

<edit><delete><entire row>

This is the final report format:
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
M

Mr. Low

Dear Ron,

I have tried your steps, it worked out very well.

Many thanks for your help.


Kind Regars

Low Seng Kuang
Malaysia

--
A36B58K641


Ron Coderre said:
Try this:

With your posted report data beginning in A2

Insert a column before the data (the report now begins in B2)
A2: =IF(C2="",B2,A1)
Copy that formula down as far as you need

The report now looks like this:
Ref No 1 Ref No 1
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
0
Ref No 2 Ref No 2
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Select Col_A
<edit><copy>
<edit><paste special>
Check: Values....then click [OK]

Next, assuming Col_C blanks indicate rows to be deleted
Select Col_C
Press the [F5] key....that's the shortcut for <edit><go to>
Click the [Special] button
Check: Blanks.....then click the [OK] button
(now, all of the blank cells in Col_C are selected

<edit><delete><entire row>

This is the final report format:
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


Mr. Low said:
Dear Sir,

I am using Seagate Infordesktop Software which generate report in MS Excel
2002 format.

My report is designed in such a way that a reference number located the to
right hand coner of each block od data.

I need to edit the data so that it appear at the first column of each line.

Original Report Format
Ref No 1
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx

Ref No 2
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx


Edited Report Format (what I need)
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx

Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx

Is there any short cut or key board command to do this instead of copy the
reference number for each block and past each blank cell one by one?

I would be glad if someone who is knowledgable in Ms Excel 2002 could assist
me.

Thanks

Low Seng Kuang
Malaysia
Click to expand...
Click to expand...
 
R

Ron Coderre

Thanks for the feedback....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP


Mr. Low said:
Dear Ron,

I have tried your steps, it worked out very well.

Many thanks for your help.


Kind Regars

Low Seng Kuang
Malaysia

--
A36B58K641


Ron Coderre said:
Try this:

With your posted report data beginning in A2

Insert a column before the data (the report now begins in B2)
A2: =IF(C2="",B2,A1)
Copy that formula down as far as you need

The report now looks like this:
Ref No 1 Ref No 1
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
0
Ref No 2 Ref No 2
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Select Col_A
<edit><copy>
<edit><paste special>
Check: Values....then click [OK]

Next, assuming Col_C blanks indicate rows to be deleted
Select Col_C
Press the [F5] key....that's the shortcut for <edit><go to>
Click the [Special] button
Check: Blanks.....then click the [OK] button
(now, all of the blank cells in Col_C are selected

<edit><delete><entire row>

This is the final report format:
Ref No 1 xxxxx xxxxx xxxx
Ref No 1 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx
Ref No 2 xxxxx xxxxx xxxx

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


Mr. Low said:
Dear Sir,

I am using Seagate Infordesktop Software which generate report in MS Excel
2002 format.

My report is designed in such a way that a reference number located the to
right hand coner of each block od data.

I need to edit the data so that it appear at the first column of each line.

Original Report Format
Ref No 1
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx

Ref No 2
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx
xxxxx xxxxx xxxx


Edited Report Format (what I need)
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx
Ref No 1 xxxxx xxxxx xxxxx

Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx
Ref No 2 xxxxx xxxxx xxxxx

Is there any short cut or key board command to do this instead of copy the
reference number for each block and past each blank cell one by one?

I would be glad if someone who is knowledgable in Ms Excel 2002 could assist
me.

Thanks

Low Seng Kuang
Malaysia
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to eliminate empty rows in Excel 2002 worksheet ? 7
Carrying out calculations on segments of data from within a workbo 1
Comparing 2 blocks of text and data 2
counting blocks of data 4
Excel 2002 - Internet Explorer Windows opening up uncontrollably 0
use sumif / Sumproduct for year data 5
Profit & Loss Report in MS Access 2007 0
SUMIFs for particular cells in blocks of data 2

Top