| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relocating reference numbers for many blocks of data
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 -- A36B58K641 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Relocating reference numbers for many blocks of data
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" wrote: 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 -- A36B58K641 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Relocating reference numbers for many blocks of data
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" wrote: 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" wrote: 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 -- A36B58K641 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Relocating reference numbers for many blocks of data
Thanks for the feedback....I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "Mr. Low" wrote: 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" wrote: 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" wrote: 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 -- A36B58K641 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ranking query | Excel Discussion (Misc queries) | |||
| I need to store reference data in one file for any WB to ref | Excel Discussion (Misc queries) | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Linest: problems using broken reference data | Excel Worksheet Functions | |||
Linear Mode
