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

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

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

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

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

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

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