Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
256 columns X 2000 rows = 640000 rows. That's far above Excel limits!
-- AP "GaryG" a écrit dans le message de ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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" a écrit dans le message de ... 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
OK, you have a value in sheet1!A1 that you need to verify exists in the data
held in sheet2!$A$1:$IV$65536. This formula in say sheet1!A2 will return a TRUE or FALSE (1 or 0) answer depending if the test value is in the 'matrix'. =SUMPRODUCT(--(sheet2!$A$1:$IV$65536=sheet1!$A$1)) HTH Giz "GaryG" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
Cheers for your help but this isn't working for me. This is the forumla i
entered =SUMPRODUCT(--(pallets!$1:$65536=moves!$A$1)) Sheet 2 being "pallets" which is the matrix Sheet 1 being "moves" which holds the data i want to check exists in "pallets" Error is #NUM "Gizmo63" wrote: OK, you have a value in sheet1!A1 that you need to verify exists in the data held in sheet2!$A$1:$IV$65536. This formula in say sheet1!A2 will return a TRUE or FALSE (1 or 0) answer depending if the test value is in the 'matrix'. =SUMPRODUCT(--(sheet2!$A$1:$IV$65536=sheet1!$A$1)) HTH Giz "GaryG" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
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" wrote in message ... 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" a écrit dans le message de ... 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
The formula is correct and I've double checked that it works for text or
numeric arguments. You need to go over your 'matrix', somewhere in there is an error producing the excel #NUM! error message. Quickest way is to select the entire sheet, CTRL+F to open the Find, then look for #NUM! being sure to select 'Look in Values' in the options. Giz "GaryG" wrote: Cheers for your help but this isn't working for me. This is the forumla i entered =SUMPRODUCT(--(pallets!$1:$65536=moves!$A$1)) Sheet 2 being "pallets" which is the matrix Sheet 1 being "moves" which holds the data i want to check exists in "pallets" Error is #NUM "Gizmo63" wrote: OK, you have a value in sheet1!A1 that you need to verify exists in the data held in sheet2!$A$1:$IV$65536. This formula in say sheet1!A2 will return a TRUE or FALSE (1 or 0) answer depending if the test value is in the 'matrix'. =SUMPRODUCT(--(sheet2!$A$1:$IV$65536=sheet1!$A$1)) HTH Giz "GaryG" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Rows into Columns
Sorry i don't understand the code. How do i go about inserting this ?
"PY & Associates" wrote: 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" wrote in message ... 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" a écrit dans le message de ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
How to reverse order of rows and columns | Excel Discussion (Misc queries) | |||
Changing Columns to Rows Question | Excel Discussion (Misc queries) | |||
hidden rows & columns slow file open | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |