Changing Rows into Columns

I have data on 2000 rows going across X amount of columns. What i need is to
sort the data so everything appears in Column A one after another so i can
use it for a VLookup.

Is there any way of doing this?

Hi Gary,

Your problem is not very clear from your post. Are you trying to rotate all
your data from rows to columns? If that is the case you could just leave it
as is and use HLOOKUP.

Cheers Martin but the HLOOKUP doesn't help me.

What i am trying to end up with is all my data cells to appear in one
complete column. Now i have data spread across rows & columns similar to
data in a matrix.

Another way of looking at it is that i need the data in column B to appear
under the data currently in ColumnA, then Column C data to appear under
this & so on.

I can do it manually but i have 256 columns of data so looking for a quick
way.

"MartinW" wrote:

> Hi Gary,
>
> Your problem is not very clear from your post. Are you trying to rotate all
> your data from rows to columns? If that is the case you could just leave it
> as is and use HLOOKUP.
>
>
>

Ok Gary now I can understand your problem, unfortunately, I can't help you
other than to say I think it should be able to be done. Hopefully it won't
be long before one of the resident Gurus drops by and enlightens us both.

Cheers
Martin

Judging by your posts what I guess you're trying to achieve is a check to see
if a particular value exists in your matrix.

This is the only reason I can see for creating a vlookup on 1 column only.

Can you confirm? If I'm right I can give you an answer.

Giz

"MartinW" wrote:

> Ok Gary now I can understand your problem, unfortunately, I can't help you
> other than to say I think it should be able to be done. Hopefully it won't
> be long before one of the resident Gurus drops by and enlightens us both.
>
> Cheers
> Martin
>
>
>

Hi, thats correct i have a lot of data scattered across columns & rows, i
want to use a vlookup on another sheet to return these values.

"Gizmo63" wrote:

> Judging by your posts what I guess you're trying to achieve is a check to see
> if a particular value exists in your matrix.
>
> This is the only reason I can see for creating a vlookup on 1 column only.
>
> Can you confirm? If I'm right I can give you an answer.
>
> Giz
>
> "MartinW" wrote:
>
> > Ok Gary now I can understand your problem, unfortunately, I can't help you
> > other than to say I think it should be able to be done. Hopefully it won't
> > be long before one of the resident Gurus drops by and enlightens us both.
> >
> > Cheers
> > Martin
> >
> >
> >

256 columns X 2000 rows = 640000 rows. That's far above Excel limits!

--
AP

"GaryG" <GaryG@discussions.microsoft.com> a écrit dans le message de
news:D1AE17A5-B5C8-4AA3-8562-17BAC5C997DA@microsoft.com...
>
> Cheers Martin but the HLOOKUP doesn't help me.
>
> What i am trying to end up with is all my data cells to appear in one
> complete column. Now i have data spread across rows & columns similar to
> data in a matrix.
>
> Another way of looking at it is that i need the data in column B to appear
> under the data currently in ColumnA, then Column C data to appear under
> this & so on.
>
> I can do it manually but i have 256 columns of data so looking for a quick
> way.
>
> "MartinW" wrote:
>
> > Hi Gary,
> >
> > Your problem is not very clear from your post. Are you trying to rotate
all
> > your data from rows to columns? If that is the case you could just leave
it
> > as is and use HLOOKUP.
> >
> >
> >

Sorry not all rows have the 256 columns populated, most have only 1 column
per row, however 1 or 2 have 256 columns.

It should only amount to about 7000 data cells.

"Ardus Petus" wrote:

> 256 columns X 2000 rows = 640000 rows. That's far above Excel limits!
>
> --
> AP
>
> "GaryG" <GaryG@discussions.microsoft.com> a écrit dans le message de
> news:D1AE17A5-B5C8-4AA3-8562-17BAC5C997DA@microsoft.com...
> >
> > Cheers Martin but the HLOOKUP doesn't help me.
> >
> > What i am trying to end up with is all my data cells to appear in one
> > complete column. Now i have data spread across rows & columns similar to
> > data in a matrix.
> >
> > Another way of looking at it is that i need the data in column B to appear
> > under the data currently in ColumnA, then Column C data to appear under
> > this & so on.
> >
> > I can do it manually but i have 256 columns of data so looking for a quick
> > way.
> >
> > "MartinW" wrote:
> >
> > > Hi Gary,
> > >
> > > Your problem is not very clear from your post. Are you trying to rotate
> all
> > > your data from rows to columns? If that is the case you could just leave
> it
> > > as is and use HLOOKUP.
> > >
> > >
> > >
>
>
>

Try this

dim c as range
dim rng as range
set rng=range("A1").currentregion
i=3000
for each c in rng
if not isempty(c) then
cells(i,1)=c
i=i+1
end if
next
rows(1:2999).delete 'if you want

"GaryG" <GaryG@discussions.microsoft.com> wrote in message
news:A08CC7DF-3C10-417D-B2C5-0A209457B580@microsoft.com...
> Sorry not all rows have the 256 columns populated, most have only 1
column
> per row, however 1 or 2 have 256 columns.
>
> It should only amount to about 7000 data cells.
>
> "Ardus Petus" wrote:
>
> > 256 columns X 2000 rows = 640000 rows. That's far above Excel limits!
> >
> > --
> > AP
> >
> > "GaryG" <GaryG@discussions.microsoft.com> a écrit dans le message de
> > news:D1AE17A5-B5C8-4AA3-8562-17BAC5C997DA@microsoft.com...
> > >
> > > Cheers Martin but the HLOOKUP doesn't help me.
> > >
> > > What i am trying to end up with is all my data cells to appear in one
> > > complete column. Now i have data spread across rows & columns
similar to
> > > data in a matrix.
> > >
> > > Another way of looking at it is that i need the data in column B to
appear
> > > under the data currently in ColumnA, then Column C data to appear
under
> > > this & so on.
> > >
> > > I can do it manually but i have 256 columns of data so looking for a
quick
> > > way.
> > >
> > > "MartinW" wrote:
> > >
> > > > Hi Gary,
> > > >
> > > > Your problem is not very clear from your post. Are you trying to
rotate
> > all
> > > > your data from rows to columns? If that is the case you could just
leave
> > it
> > > > as is and use HLOOKUP.
> > > >
> > > >
> > > >
> >
> >
> >