Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
Hi,
In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
Witold
The only way I know is by macro. I think that your data is in two columns, but it does not matter if it is in 1. However, its better to copy the data before running the macro. Copy the macro into the VB Editor, select the data and run the macro. Sub CreateList() ' select Both columns ' make sure columns c .... z are blank r = ActiveCell.Row - 1: col = ActiveCell.Column nr = Selection.Rows.Count For Each c In Selection If IsEmpty(c) Then 'Do nothing ElseIf IsNumeric(c) Then col = 1: r = r + 1: Count = 1 Cells(r, col) = c ElseIf Not IsNumeric(c) Then Count = Count + 1 Cells(r, Count) = c End If Next 'remove data from remaining rows Range(Cells(r + 1, 1), Cells(nr + 1, 2)).ClearContents 'remove selection Range("A1").Select End Sub regards Peter "Witold" wrote: Hi, In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
Peter
Thanks for that, but it doesn't seem to work for me. I arranged the sample data exactly as I had shown in example, selected it, run the macro and I got this: 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 which is not quite what I need. Any thoughts? I use Excel 2003, if that matters. Just to clarify - sets of data in column B "between" numbers in column A can be anything from 0 to x number of entries. In case of 0 it should look like this: A1 B1 2343 x234 x345 4567 3453 x564 x345 x543 into: A1 B1 C1 2343 x234 1 x345 2 4567 1 3453 x564 1 x345 2 x543 3 Thanks again! Witold "Peter" wrote: The only way I know is by macro. I think that your data is in two columns, but it does not matter if it is in 1. However, its better to copy the data before running the macro. Copy the macro into the VB Editor, select the data and run the macro. Sub CreateList() ' select Both columns ' make sure columns c .... z are blank r = ActiveCell.Row - 1: col = ActiveCell.Column nr = Selection.Rows.Count For Each c In Selection If IsEmpty(c) Then 'Do nothing ElseIf IsNumeric(c) Then col = 1: r = r + 1: Count = 1 Cells(r, col) = c ElseIf Not IsNumeric(c) Then Count = Count + 1 Cells(r, Count) = c End If Next 'remove data from remaining rows Range(Cells(r + 1, 1), Cells(nr + 1, 2)).ClearContents 'remove selection Range("A1").Select End Sub regards Peter "Witold" wrote: Hi, In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
From your example, it looks like you could just select all the data in column
B and move it up one row. Hope this helps, Hutch "Witold" wrote: Hi, In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
How obvious! But it leaves empty row every couple of rows. Any way to
remove them quickly? Thanks Witold Tom Hutchins wrote: From your example, it looks like you could just select all the data in column B and move it up one row. Hope this helps, Hutch "Witold" wrote: Hi, In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
OK
With your original data this will present as you wanted. I misunderstood before. Sub CreateList2() ' select Both columns ' make sure columns c .... z are blank r = ActiveCell.Row - 1: col = ActiveCell.Column nr = Selection.Rows.Count For Each c In Selection If IsEmpty(c) Then 'Do nothing ElseIf IsNumeric(c) Then Count = 0 col = 1: r = r + 1 Cells(r, col) = c: ElseIf Not IsNumeric(c) Then Count = Count + 1 If Count 1 Then r = r + 1 End If Cells(r, col + 1) = c Cells(r, col + 2) = Count End If Next 'remove data from remaining rows Range(Cells(r + 1, 1), Cells(nr + 1, 2)).ClearContents 'remove selection Range("A1").Select End Sub Regards Peter "Witold" wrote: How obvious! But it leaves empty row every couple of rows. Any way to remove them quickly? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
In the first empty column (let's assume it's C), number all the rows
sequentially from the first row with data to the last row with data. Now sort all the data by column B. All the blank rows should be together. Select and delete those rows, then sort the data in ascending order by column C. Delete column C, and you should be done. Hope this helps, Hutch "Witold" wrote: How obvious! But it leaves empty row every couple of rows. Any way to remove them quickly? Thanks Witold Tom Hutchins wrote: From your example, it looks like you could just select all the data in column B and move it up one row. Hope this helps, Hutch "Witold" wrote: Hi, In short, currently my data looks like this: A1 B1 2343 x234 x345 4567 x567 x678 x484 x444 3453 x564 x345 x543 etc. While I would need it like this: A1 B1 C1 2343 x234 1 x345 2 4567 x567 1 x678 2 x484 3 x444 4 3453 x564 1 x345 2 x543 3 I hope it's self explanatory and makes sense. Could somebody please help me how to do this? I've got couple of thousands of these records, everything is ready, it's a matter of one time conversion only. Thank you. -- Witold |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rearranging data | Excel Worksheet Functions | |||
Rearranging Data Within a Cell | Excel Worksheet Functions | |||
Rearranging Data in Excel | Excel Discussion (Misc queries) | |||
Rearranging the layout of data | Excel Discussion (Misc queries) | |||
Rearranging Data Help... | Excel Discussion (Misc queries) |