Home |
Search |
Today's Posts |
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unexpected line breaks in .csv | Excel Discussion (Misc queries) | |||
flip spreadsheet data from row records to column record fields | Excel Discussion (Misc queries) | |||
flip spreadsheet data from row records to column record fields | Excel Discussion (Misc queries) | |||
Line chart - date line association gone mad! | Charts and Charting in Excel | |||
Macro - Skipping a line | Excel Worksheet Functions |