If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fields in a record are on the next line
I have a SS with six fields as follows:
Record 1- A1, B1, C1, D1, A2, B2 I want to move A2 to E1 and B2 to F1 so Record 1 is on one line. The present structure now is first record: A1 B1 C1 D1 A2 B2 Line 3 Blank line and the next line (4) is the second record. A4 B4 C4 D4 A5 B5 Line 6 Blank line etc. There are 2600 records like this. How can I correct this problem, automated, if possible. Thanks, Dan |
#2
|
|||
|
|||
It won't be too bad manually.
Put this in E1: =IF(MOD(ROW(),3)=1,A2,NA()) Put this in F1: =IF(MOD(ROW(),3)=1,B2,NA()) And drag down your 2600 rows. You'll end up with errors on row 2,3, 5,6, 8,9, ... Now you can select columns E:F and edit|copy edit|paste special|Values Select column E edit|goto|special constants (but only leave errors checked) rightclick on any of the selected cells and choose delete delete entire row. Dan wrote: I have a SS with six fields as follows: Record 1- A1, B1, C1, D1, A2, B2 I want to move A2 to E1 and B2 to F1 so Record 1 is on one line. The present structure now is first record: A1 B1 C1 D1 A2 B2 Line 3 Blank line and the next line (4) is the second record. A4 B4 C4 D4 A5 B5 Line 6 Blank line etc. There are 2600 records like this. How can I correct this problem, automated, if possible. Thanks, Dan -- Dave Peterson |
#3
|
|||
|
|||
Thanks, Dave. I'll try it tomorrow and let you know.
Dan "Dave Peterson" wrote: It won't be too bad manually. Put this in E1: =IF(MOD(ROW(),3)=1,A2,NA()) Put this in F1: =IF(MOD(ROW(),3)=1,B2,NA()) And drag down your 2600 rows. You'll end up with errors on row 2,3, 5,6, 8,9, ... Now you can select columns E:F and edit|copy edit|paste special|Values Select column E edit|goto|special constants (but only leave errors checked) rightclick on any of the selected cells and choose delete delete entire row. Dan wrote: I have a SS with six fields as follows: Record 1- A1, B1, C1, D1, A2, B2 I want to move A2 to E1 and B2 to F1 so Record 1 is on one line. The present structure now is first record: A1 B1 C1 D1 A2 B2 Line 3 Blank line and the next line (4) is the second record. A4 B4 C4 D4 A5 B5 Line 6 Blank line etc. There are 2600 records like this. How can I correct this problem, automated, if possible. Thanks, Dan -- Dave Peterson |
#4
|
|||
|
|||
Thanks, Dave. That works for me!
"Dan" wrote: Thanks, Dave. I'll try it tomorrow and let you know. Dan "Dave Peterson" wrote: It won't be too bad manually. Put this in E1: =IF(MOD(ROW(),3)=1,A2,NA()) Put this in F1: =IF(MOD(ROW(),3)=1,B2,NA()) And drag down your 2600 rows. You'll end up with errors on row 2,3, 5,6, 8,9, ... Now you can select columns E:F and edit|copy edit|paste special|Values Select column E edit|goto|special constants (but only leave errors checked) rightclick on any of the selected cells and choose delete delete entire row. Dan wrote: I have a SS with six fields as follows: Record 1- A1, B1, C1, D1, A2, B2 I want to move A2 to E1 and B2 to F1 so Record 1 is on one line. The present structure now is first record: A1 B1 C1 D1 A2 B2 Line 3 Blank line and the next line (4) is the second record. A4 B4 C4 D4 A5 B5 Line 6 Blank line etc. There are 2600 records like this. How can I correct this problem, automated, if possible. Thanks, Dan -- Dave Peterson |
#5
|
|||
|
|||
And not too bad if you only have to do it once (or twice...)
Dan wrote: Thanks, Dave. That works for me! "Dan" wrote: Thanks, Dave. I'll try it tomorrow and let you know. Dan "Dave Peterson" wrote: It won't be too bad manually. Put this in E1: =IF(MOD(ROW(),3)=1,A2,NA()) Put this in F1: =IF(MOD(ROW(),3)=1,B2,NA()) And drag down your 2600 rows. You'll end up with errors on row 2,3, 5,6, 8,9, ... Now you can select columns E:F and edit|copy edit|paste special|Values Select column E edit|goto|special constants (but only leave errors checked) rightclick on any of the selected cells and choose delete delete entire row. Dan wrote: I have a SS with six fields as follows: Record 1- A1, B1, C1, D1, A2, B2 I want to move A2 to E1 and B2 to F1 so Record 1 is on one line. The present structure now is first record: A1 B1 C1 D1 A2 B2 Line 3 Blank line and the next line (4) is the second record. A4 B4 C4 D4 A5 B5 Line 6 Blank line etc. There are 2600 records like this. How can I correct this problem, automated, if possible. Thanks, Dan -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can I link 1 form to several tables? | Mico | Using Forms | 7 | July 6th, 2005 07:36 PM |
Creating Records in tables automatically | peterg290935 | Using Forms | 8 | June 22nd, 2005 08:12 AM |
Repost: Record 1 contained too few data fields | HJ | Mailmerge | 0 | May 20th, 2005 01:27 PM |
Access Mail Merge to Word.doc files ? | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 18th, 2005 06:31 PM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |