| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
This is probably simple to fix
I have a spreadsheet (Rather Large) that comes from a report I run each month That I need to sort various ways. The problem is the report layout. It is layed out like this: Name data1 data2 data3 data1 data2 data3 name2 data1 data2 data3 data1 data2 data3 data1 data2 data3 data1 data2 data3 name3 data1 data2 data3 data1 data2 data3 There is no way of knowing how many data lines there are for each name. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. This takes me 3 hours sometimes. Anyone got an automated idea for doing this? -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 |
|
#2
|
|||
|
|||
|
1st: Insert another column before A, use this to number from 1 - xxx.. (***
lets you return to the correct sorting order if you accidently get screwed up) 2nd: Use the subtotal function.. instead of a TOTAL, you can do a count, average, etc.. HTH "benplace" wrote: I have a spreadsheet (Rather Large) that comes from a report I run each month That I need to sort various ways. The problem is the report layout. It is layed out like this: Name data1 data2 data3 data1 data2 data3 name2 data1 data2 data3 data1 data2 data3 data1 data2 data3 data1 data2 data3 name3 data1 data2 data3 data1 data2 data3 There is no way of knowing how many data lines there are for each name. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. This takes me 3 hours sometimes. Anyone got an automated idea for doing this? -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 |
|
#3
|
|||
|
|||
|
Is there any indication at all on the cells that contain the name.
I don't see anything in your sample data. Are you sure your text file looks the same way--sometimes importing it will squeeze out those leading spaces? benplace wrote: I have a spreadsheet (Rather Large) that comes from a report I run each month That I need to sort various ways. The problem is the report layout. It is layed out like this: Name data1 data2 data3 data1 data2 data3 name2 data1 data2 data3 data1 data2 data3 data1 data2 data3 data1 data2 data3 name3 data1 data2 data3 data1 data2 data3 There is no way of knowing how many data lines there are for each name. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. This takes me 3 hours sometimes. Anyone got an automated idea for doing this? -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 -- Dave Peterson |
|
#4
|
|||
|
|||
|
The problem is I want to do sorting and make sure the data for each person stays attached to that person. However the persons name isnt on each data row, but above the data. -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 |
|
#5
|
|||
|
|||
|
I want to be able to reference data1 and know it belongs to that person. One thing that may help is that the name field is in italics. Is there a way to check column a for italics, then repeat the name until you run into another italics name? Maybe a dumb question. If I can figure this out it will save 3 hours of work a day. Dave Peterson Wrote: Is there any indication at all on the cells that contain the name. I don't see anything in your sample data. Are you sure your text file looks the same way--sometimes importing it will squeeze out those leading spaces? benplace wrote: I have a spreadsheet (Rather Large) that comes from a report I run each month That I need to sort various ways. The problem is the report layout. It is layed out like this: Name data1 data2 data3 data1 data2 data3 name2 data1 data2 data3 data1 data2 data3 data1 data2 data3 data1 data2 data3 name3 data1 data2 data3 data1 data2 data3 There is no way of knowing how many data lines there are for each name. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. This takes me 3 hours sometimes. Anyone got an automated idea for doing this? -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 -- Dave Peterson -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 |
|
#6
|
|||
|
|||
|
Ahhh. If the cell in column A is italics, then that means it's a name?
But before we continue, you wrote this in your initial post. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. But your data looked like you had names and data intermingled in column A. Does your data really look like: colA ColB ColC Name data1 data2 data3 So if you could verify what your input data looks like and what you want it to look like when you're done, it would make it easier than guessing. I think it would be easiest to make your data look like: ColA ColB Name Data1 Name Data2 Name Data3 But you may not agree. benplace wrote: I want to be able to reference data1 and know it belongs to that person. One thing that may help is that the name field is in italics. Is there a way to check column a for italics, then repeat the name until you run into another italics name? Maybe a dumb question. If I can figure this out it will save 3 hours of work a day. Dave Peterson Wrote: Is there any indication at all on the cells that contain the name. I don't see anything in your sample data. Are you sure your text file looks the same way--sometimes importing it will squeeze out those leading spaces? benplace wrote: I have a spreadsheet (Rather Large) that comes from a report I run each month That I need to sort various ways. The problem is the report layout. It is layed out like this: Name data1 data2 data3 data1 data2 data3 name2 data1 data2 data3 data1 data2 data3 data1 data2 data3 data1 data2 data3 name3 data1 data2 data3 data1 data2 data3 There is no way of knowing how many data lines there are for each name. What I have been doing is highlighting the name and grabbing the black bar and pulling it down so the name is on each line. This takes me 3 hours sometimes. Anyone got an automated idea for doing this? -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 -- Dave Peterson -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 -- Dave Peterson |
|
#7
|
|||
|
|||
|
Thought of this in the middle of the night. lol I dont care about data in column A If I had a macro the read cell A1, checked if it contained a number or was blank. If this was not true, meaning it was a name, it would copy that name, check the next cell A2 and if it were blank or numeric, paste the name, then A3 and so on. When it hits a name, it would then copy that name, and go to the next cell down. It could repeat this until it hit 20 straight blank cells. Then end. I could run the macro, come back and go to the bottom and remove the 20 blanks. I dont think this is simple anymore. lol -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 |
|
#8
|
|||
|
|||
|
So you're just looking at column A.
If the cell contains numbers or is blank then replace it with the value from the cell above? You could loop through the cells looking for this kind of stuff and process it row by row--or you could let excel do some of the work. Manually, I'd do this assuming that there are no formulas in column A. Select column A edit|goto|special click constants and check only Numbers click ok hit the delete key (to clear those cells) Then select column A edit|goto|special click blanks type = and hit the uparrow then hit ctrl-enter You've now filled each blank cell with a formula that points to the cell above. Now convert it to values select column A edit|copy edit|paste special|values In code... Option Explicit Sub testme01() Dim wks As Worksheet Set wks = ActiveSheet With wks If IsEmpty(.Range("a1")) Then MsgBox "Please put something in A1!" Exit Sub End If 'the "on error resume next" is used 'just in case there are no numbers in that Column 'and just in case there are no blanks On Error Resume Next .Range("a:a").Cells _ .SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) .FormulaR1C1 _ = "=r[-1]c" On Error GoTo 0 With .Range("a:a") .Value = .Value End With End With End Sub benplace wrote: Thought of this in the middle of the night. lol I dont care about data in column A If I had a macro the read cell A1, checked if it contained a number or was blank. If this was not true, meaning it was a name, it would copy that name, check the next cell A2 and if it were blank or numeric, paste the name, then A3 and so on. When it hits a name, it would then copy that name, and go to the next cell down. It could repeat this until it hit 20 straight blank cells. Then end. I could run the macro, come back and go to the bottom and remove the 20 blanks. I dont think this is simple anymore. lol -- benplace ------------------------------------------------------------------------ benplace's Profile: http://www.excelforum.com/member.php...o&userid=27446 View this thread: http://www.excelforum.com/showthread...hreadid=469551 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| simple if then function | Excel Worksheet Functions | |||
| Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
| Help with what should be a simple formula | Excel Worksheet Functions | |||
| Simple But Stumped | Excel Discussion (Misc queries) | |||
| Ploting dates against a calendar and not as a simple events | Charts and Charting in Excel | |||
Hybrid Mode
