Converting a table to a single column

[Syed Tanveer]

Pl help me solve this problem that I am handling from quite some time.

In the attachment, sheet1 contains data (roll numbers of students).
In fact it is a pdf file converted to excel.
In sheet2, I need all the roll numbers in a single column (as shown in sheet2).

I have been doing this manually (copying each row, transposing it and pasting in sheet2) which consumes a lot and lot of time.
Please show me some shortcut/formula to do this.

 

[Somendra Misra]

Hi Syed,

See the file.

I had noticed that all the roll no. are starting from "13" so written a code to get all the roll no. on Sheet2 Column B.

You can change the destination. Also note to define the entire data as Data Named Range.

Regards,

 

[Syed Tanveer]

Hello Mr.Somendra!
Hope you are doing fine !
First of all, let me thank you for your macro which has saved a lot of time for me!
Let me put in brief about that macro:
It copies a range of data in sheet1 to a single column in sheet2.
The data(student roll numbers) in sheet1 was selected based on the first two digits (in this case "13").
In some cases the student roll numbers may also start with 09, 10, 11, 12 also.
How to include this condition(s) in the macro?

Please update

PFA sample file for reference.

 

[Somendra Misra]

Hello Mr.Somendra!
Hope you are doing fine !
First of all, let me thank you for your macro which has saved a lot of time for me!
Let me put in brief about that macro:
It copies a range of data in sheet1 to a single column in sheet2.
The data(student roll numbers) in sheet1 was selected based on the first two digits (in this case "13").
In some cases the student roll numbers may also start with 09, 10, 11, 12 also.
How to include this condition(s) in the macro?

Please update

PFA sample file for reference.

Will update something by tomorrow on this.

Regards,

 

[Syed Tanveer]

Thanks.
Awaiting your update !

 

[Syed Tanveer]

Hello Somendra !
I am still awaiting your update on my problem. I guess you are held up with some thing else!
Meanwhile, I have tried a small edition to the macro.
I have replaced "13" with "1" so that I get all roll numbers starting with 1 (10, 11, 12, 13,...) but the problem is with roll numbers starting with "0".
Please update.
Thanks

 

[NARAYANK991]

Hi Tanveer ,

See if this is OK.

Narayan

 

[Syed Tanveer]

Thank you very much Narayan
It does exactly what I need.
Thank you very much!!!

 

[Syed Tanveer]

Hi everybody !!!
Is there any method to merge the data in different sheets into a single sheet?

I have data which is broken up into 70 sheets (sheet1, sheet2, ....sheet70)
I need all the data to be in sheet1 and also aligned subsequently by columns i.e
data in column A-sheet2 should go below data in column A-sheet1.
Data in column B-sheet2 should go below column B-sheet1 and so on.

Please suggest some way of doing this.

 

[Syed Tanveer]

I am attaching a sample file for my last post.