Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
I have a large spreadsheet (9500 lines) with data organized in the following
way: name blank hire date starting salary title blank blank current salary I need to reorganize this information into a single line format which reads: name title hire date starting salary current salary Because of the length of the document I need to find the best, automatic way to accomplish this change. I'm new to Excel 2007 so would be grateful for any, simplified assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
Hi Paddy;
? What I understood is that you have every record in two lines. In other words, total of fields 8 but splitted in two lines. Is this also true for the data? Eg: name blank hire date starting salary title blank blank current salary Robert blank 07/14/1998 $ 50,000 Manager blank blank 75,000 Mike blank 09/03/1982 25,000 Assistant blank blank 65,000 Please confirm Argy "PaddyR" wrote: I have a large spreadsheet (9500 lines) with data organized in the following way: name blank hire date starting salary title blank blank current salary I need to reorganize this information into a single line format which reads: name title hire date starting salary current salary Because of the length of the document I need to find the best, automatic way to accomplish this change. I'm new to Excel 2007 so would be grateful for any, simplified assistance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
One more question, if what I said is true, is there any spaces in between the
data? And considering that as a true conclusion, what you really have is (9500/2) = 4,750 records. Is this correct? Argy "PaddyR" wrote: I have a large spreadsheet (9500 lines) with data organized in the following way: name blank hire date starting salary title blank blank current salary I need to reorganize this information into a single line format which reads: name title hire date starting salary current salary Because of the length of the document I need to find the best, automatic way to accomplish this change. I'm new to Excel 2007 so would be grateful for any, simplified assistance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
What you say is true. In my example, Row 1 contains cells A1 (Name) B1(empty
cell) C1 (Hire Date) & D1 (Starting Salary) Row 2 contains cells A2 (Title) B2 (empty cell) C2 (empty cell) & D2 (Current Salary) The second record includes the same information on Row 3 and Row 4. The third record includes the same information on Row 5 and Row 6, etc. In my new spreadsheet, for purposes of sorting and analysis, I need to transfer cells A1 (described above) to A1; A2 to B1; C1 to C1; D1 to D1; and D2 to E1. It's also possible to perform this task by simply moving cells around in the existing spreadsheet and adding new headings. But, with 4,750 records I need to find a shortcut option that works across the entire list. Thanks for your response! PaddR "Argy - Arcasoft" wrote: One more question, if what I said is true, is there any spaces in between the data? And considering that as a true conclusion, what you really have is (9500/2) = 4,750 records. Is this correct? Argy "PaddyR" wrote: I have a large spreadsheet (9500 lines) with data organized in the following way: name blank hire date starting salary title blank blank current salary I need to reorganize this information into a single line format which reads: name title hire date starting salary current salary Because of the length of the document I need to find the best, automatic way to accomplish this change. I'm new to Excel 2007 so would be grateful for any, simplified assistance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
Insert a new worksheet and put the formulae below in the cells stated:
A1: =INDIRECT("Sheet1!A"&2*ROW()-1) B1: =INDIRECT("Sheet1!A"&2*ROW()) C1: =INDIRECT("Sheet1!C"&2*ROW()-1) D1: =INDIRECT("Sheet1!D"&2*ROW()-1) E1: =INDIRECT("Sheet1!D"&2*ROW()) These will give you the headings from Sheet1, but copy them down and you will have the data arranged as you would like. You might like to format column C as a date, and D and E as currency. When you are done you can fix the values, and that will allow you to delete the original data (make sure you have a backup copy of that in case you might want it for something else). Hope this helps. Pete On Nov 13, 10:44*am, PaddyR wrote: What you say is true. *In my example, Row 1 contains cells A1 (Name) B1(empty cell) C1 (Hire Date) & D1 (Starting Salary) Row 2 contains cells A2 (Title) B2 (empty cell) C2 (empty cell) & D2 (Current Salary) The second record includes the same information on Row 3 and Row 4. The third record includes the same information on Row 5 and Row 6, etc. In my new spreadsheet, for purposes of sorting and analysis, I need to transfer cells A1 (described above) to A1; A2 to B1; C1 to C1; D1 to D1; and D2 to E1. It's also possible to perform this task by simply moving cells around in the existing spreadsheet and adding new headings. *But, with 4,750 records I need to find a shortcut option that works across the entire list. Thanks for your response! PaddR "Argy - Arcasoft" wrote: One more question, if what I said is true, is there any spaces in between the data? And considering that as a true conclusion, what you really have is (9500/2) = 4,750 records. Is this correct? Argy "PaddyR" wrote: I have a large spreadsheet (9500 lines) with data organized in the following way: name * * * blank * * * *hire date * * * *starting salary title * * * * *blank * * * *blank * * * * * * current salary I need to reorganize this information into a single line format which reads: name * * title * * hire date * * starting salary * * current salary * Because of the length of the document I need to find the best, automatic way to accomplish this change. *I'm new to Excel 2007 so would be grateful for any, simplified assistance.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-formatting
Hi Paddy;
this is one of those things that although there are almost infinite ways of doing it, no matter what you choose, it will be very demanding, boring, and particularly very frustrating. The fact that there is a lot of data manipulation increments the error possibility exponentially. Of course, there is always a way to take shortcuts and with the right help, this may be resolve in a matter of minutes and with acute precision. Here is your solution and it should take you 10 minutes. You MUST follow step by step my instructions, so you get the result you expect. Here we go: 0- Before you start, Make your that the sheet where you are going to do everything is completly empty and that there is nothing on it. 1- Copy all the data starting on A2 on new sheet. If your email is correct, you have four fields, from A to D and every record is divided in two rows. I am assuming that we don't want to carry on the blanks. So they will disappear in the process. 2- Copy formula below in F2: =IF(LEN(F1)0,"",A2&"|"&TEXT(C2,"mm/dd/yyyy")&"||"&D2&"|||"&A3&"||||"&D3) 3- Copy previous formula from F2 to the last row or F(n) aprox. F9600 4- Copy formula below in H2 =IF(LEN(H1)0,"",LEFT($F2,FIND("|",$F2,1)-1)) 5- Copy formula below in I2 =IF(LEN(I1)0,"",MID($F2,FIND("|",$F2,1)+1,FIND("| |",$F2,FIND("|",$F2,1))-FIND("|",$F2,1)-1)) 6- Copy formula below in J2 =IF(LEN(J1)0,"",MID($F2,FIND("||",$F2,1)+2,FIND(" |||",$F2,FIND("||",$F2,1))-FIND("||",$F2,1)-2)) 7- Copy formula below in K2 =IF(LEN(K1)0,"",MID($F2,FIND("|||",$F2,1)+3,FIND( "||||",$F2,FIND("|||",$F2,1))-FIND("|||",$F2,1)-3)) 8- Copy formula below in L2 =IF(LEN(L1)0,"",RIGHT($F2,LEN(F2)-FIND("||||",$F2,1)-3)) 9- Copy H2:L2 to the last row with that or H(n) aprox H9600 Here you will see that the data has been grouped and that now it look like records, but still has blank rows in between. Ok the next step is to clean the file to get it in the format you need. 10- You MUST convert all formulas into values. To do this, you can take the whole sheet and click COPY and the PASTE/ PASTE SPECIAL/VALUES. This will convert everything in values. 11- Delete columns A to G. Column H now is Column A, and it is your first field. 12- Name the fields in row 1: a. A1 = Name b. B1 = Hire Date c. C1 = Starting Salary d. Title e. Current Salary 13- Do a Filter in Row 1 14- Filter out all that is blank 15- Delete the filter result; this are the in between blank rows 16- Clear the Filter and My friend, you have got your data. Steps 13 to 16 offers several ways to do them. I am sure you have used others before. Anything you feel confortable with, go ahead and use it for this last part. I would appreciate that you tell me how did it go. Argy "PaddyR" wrote: What you say is true. In my example, Row 1 contains cells A1 (Name) B1(empty cell) C1 (Hire Date) & D1 (Starting Salary) Row 2 contains cells A2 (Title) B2 (empty cell) C2 (empty cell) & D2 (Current Salary) The second record includes the same information on Row 3 and Row 4. The third record includes the same information on Row 5 and Row 6, etc. In my new spreadsheet, for purposes of sorting and analysis, I need to transfer cells A1 (described above) to A1; A2 to B1; C1 to C1; D1 to D1; and D2 to E1. It's also possible to perform this task by simply moving cells around in the existing spreadsheet and adding new headings. But, with 4,750 records I need to find a shortcut option that works across the entire list. Thanks for your response! PaddR "Argy - Arcasoft" wrote: One more question, if what I said is true, is there any spaces in between the data? And considering that as a true conclusion, what you really have is (9500/2) = 4,750 records. Is this correct? Argy "PaddyR" wrote: I have a large spreadsheet (9500 lines) with data organized in the following way: name blank hire date starting salary title blank blank current salary I need to reorganize this information into a single line format which reads: name title hire date starting salary current salary Because of the length of the document I need to find the best, automatic way to accomplish this change. I'm new to Excel 2007 so would be grateful for any, simplified assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |