Hello,
Actually I was here to find a solution, however I have found no solution therefore I had to find a solution myself; and here it is:
Firstly, my goal was aligning the numbers in a specific column as sequential (consecutive). (after 61002, 61003 must be seen instead of 877, then go on like that). However all the numbers had different spaces between them (in a column), and I cannot align them on different sheet and past as aligned. They used to look like below:
| 61000 = { |
| |
| } |
| 61001 = { |
| |
| |
| |
| |
| } |
| 61002 = { |
| |
| |
| } |
| 877= { |
<colgroup><col width="64" style="width:48pt"><tbody>
</tbody>
I aimed to align them as consecutive ( 61000, 61001, 61002, 61003, 61004,) without deleting any pharanthesis, and deleting any space (I couldn't delete even any blanks because I have some valuable data very beside them).
Step 1:
Insert new column to their left side, and new row to top of them (for filtering them later)
Then, write 1 beside our former A1 (which is 61000 = {
, and align 2,3 under new written 1. And drag them until to the end of your data (1,2,3,4....4012,4013,4014 etc.)
And write a,b,c,d, ..etc-(as much as you need) to top of them. Like:
| a | b | c |
| 1 | 61000 = { | |
| 2 | | name = "A" |
| 3 | | country = X |
<colgroup><col width="64" style="width:48pt" span="3"><tbody>
</tbody>
P.S. : If your data is too long, and it takes too long to drag, put any letter to new A1, then copy it (ctrl+c). Then quickly scroll to the end of your data from the right hold (hold and take it to the very down).
Paste your A1 to very end of your data in the same column. Then this time, copy that letter which you just have pasted (on Axx). Then; "CTRL+SHIFT+Up Arrow" and, "CTRL+V" . All column is same letter now, this time fill A2 and A3 with 1 and consequently 2. Select them altogether and click their down right corner and let excel to align it to the very end of your data.
Step2:
Copy first two column to the "Sheet 2" (new page, let's name it as
replica sheet); then copy second column and paste it to very beside it (to column C) "We name it as
replica column". Filter first row, and delete needless symbols from the third column (replica column); like;
~
| a | b | bb |
| 1 | 61000 = { | 61000 = { |
| 2 | | |
| 3 | } | |
| 4 | 61001 = { | 61001 = { |
| 5 | | |
| 6 | | |
| 7 | | |
| 8 | } | |
| 9 | 61002 = { | 61002 = { |
<colgroup><col width="64" style="width:48pt" span="3"><tbody>
</tbody>
As you see, second column has parenthesis, third one has not.
Then copy your first and third column (
replica column) to the third sheet (very new sheet) side by side.
Step 3:
In the third sheet, columns will be "a & bb"; filter them and select just bb's blanks from the filter. Then delete them all (all the rows which includes blanks between the values). Then it should be seen like this:
| a | b |
| 1 | 61000 = { |
| 4 | 61001 = { |
| 9 | 61002 = { |
<colgroup><col width="64" style="width:48pt" span="2"><tbody>
</tbody>
Beside B columnd; amend your values by aligning them like:
<colgroup><col width="64" style="width:48pt"><tbody>
</tbody>
Then after selecting them altogether, align them until the end of your data:
| 61000 |
| 61001 |
| 61002 |
| 61003 |
| 61004 |
| 61005 |
<colgroup><col width="64" style="width:48pt"><tbody>
</tbody>
For the same appearance; with B column; use the formula:
=C2&" = {"
Because of you'd used formula, you can't use this cells later. So copy all the column and paste onto itself as values (after clicking D Column, all values in the column will seen as selected, don't move your Mouse and right click to Column D again {right click to D}, and "paste special--> values"
Step 4:
Hold sheet 3 like that.
On sheet 2; delete your bb column. Then use
VLOOKUP:
Lookup value: Sheet2's A column;
Table array: Sheet3's A to D ;
col_index_sum is 2, and
Range_lookup is 0.
in fact its like below:
=VLOOKUP(A:A;Sheet3!A:D;4;0)
Step 5:
Copy C column in Sheet2 and do the same "paste values" process. Filter again all #N/A's and delete them all (
Not rows, only cells - just select them all and press "delete").
Now, select Sheet2's B column and select old values by selecting all and unselecting only blanks, paranthesises and others (because numbers are too much, you can select them one by one). *our aim is to select numbers and delete them (not rows,
only cells again) *
Now,
Select column b and press
CTRL+G --> special --> blanks , then right click one of the selected cells and delete them all by choosing "
Shift cells left".
Step 6:
We're almost finished...
Copy sheet2's first two column (we just have them anyway), then paste them on to
Sheet1's first two column.
Now, your data is aligned consecutively.
This example may not be exactly the same with your data of course, however it can give some idea, and help you out to find new solutions.
I hope it Works for you too.
