Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Filtering
I have 22000 rows of data that are sorted into two columns. column a has 10
rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! -- James |
#2
|
|||
|
|||
Jimi
On a copy of the worksheet.......................... Assuming name is cell B1 enter in C1 and drag across to L1 and down until you get zeros. =OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0) When happy,select Columns C:L then copypaste special(in place)valuesOK Insert a row at row 1 then copypaste specialtranspose the headers in A1:A10 to C1:L1 Delete columns A and B. Gord Dibben Excel MVP On Mon, 25 Apr 2005 18:13:04 -0700, "Jimipolo" wrote: I have 22000 rows of data that are sorted into two columns. column a has 10 rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! |
#3
|
|||
|
|||
many thanks for your time and effort in responding to my query. I;m not that
great in excel and although your reply is 100% correct i cannot get it to work. maybe i am using an olser version of ecxcel as it doesn't give me the option to transpose certain labels - only a tick box! can i e-mail you my spreadsheet and you can talk me through it, or alternatively post a reply as if you were advising an absolute computer 1st timer in order that i will understand!! Once again i thank you for your assistance -- James "Gord Dibben" wrote: Jimi On a copy of the worksheet.......................... Assuming name is cell B1 enter in C1 and drag across to L1 and down until you get zeros. =OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0) When happy,select Columns C:L then copypaste special(in place)valuesOK Insert a row at row 1 then copypaste specialtranspose the headers in A1:A10 to C1:L1 Delete columns A and B. Gord Dibben Excel MVP On Mon, 25 Apr 2005 18:13:04 -0700, "Jimipolo" wrote: I have 22000 rows of data that are sorted into two columns. column a has 10 rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! |
#4
|
|||
|
|||
Did you get the =offset() formula to work ok in C1:L1?
(It worked fine for me.) Then I followed Gord's instruction to copy it down the rows until I got a bunch of 0's. Then I selected columns C:L edit|copy edit|paste special|Values Then I inserted a new row 1 Then I selected A2:A11 edit|copy then I selected C1 edit|paste special|check Transpose If all that works fine (and it did for me), then I deleted columns A:B and saved it as a new name. I bet it was just a minor mistake--try it again. If it doesn't work, keep track of what you did and where it went wrong and post back with your results. But I bet you'll be sending a "thank you" to Gord! Jimipolo wrote: many thanks for your time and effort in responding to my query. I;m not that great in excel and although your reply is 100% correct i cannot get it to work. maybe i am using an olser version of ecxcel as it doesn't give me the option to transpose certain labels - only a tick box! can i e-mail you my spreadsheet and you can talk me through it, or alternatively post a reply as if you were advising an absolute computer 1st timer in order that i will understand!! Once again i thank you for your assistance -- James "Gord Dibben" wrote: Jimi On a copy of the worksheet.......................... Assuming name is cell B1 enter in C1 and drag across to L1 and down until you get zeros. =OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0) When happy,select Columns C:L then copypaste special(in place)valuesOK Insert a row at row 1 then copypaste specialtranspose the headers in A1:A10 to C1:L1 Delete columns A and B. Gord Dibben Excel MVP On Mon, 25 Apr 2005 18:13:04 -0700, "Jimipolo" wrote: I have 22000 rows of data that are sorted into two columns. column a has 10 rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! -- Dave Peterson |
#5
|
|||
|
|||
Change the DOT and AT to appropriate characters and send it along.
Gord On Wed, 27 Apr 2005 15:18:10 -0700, "Jimipolo" wrote: many thanks for your time and effort in responding to my query. I;m not that great in excel and although your reply is 100% correct i cannot get it to work. maybe i am using an olser version of ecxcel as it doesn't give me the option to transpose certain labels - only a tick box! can i e-mail you my spreadsheet and you can talk me through it, or alternatively post a reply as if you were advising an absolute computer 1st timer in order that i will understand!! Once again i thank you for your assistance |
#6
|
|||
|
|||
Oops!
Told Jimi to send book to me via email. Did not see(BAAB) your reply and explanation. Second time through with yours should get him sorted, but I will set up his book if he sends it. Gord On Wed, 27 Apr 2005 17:47:07 -0500, Dave Peterson wrote: Did you get the =offset() formula to work ok in C1:L1? (It worked fine for me.) Then I followed Gord's instruction to copy it down the rows until I got a bunch of 0's. Then I selected columns C:L edit|copy edit|paste special|Values Then I inserted a new row 1 Then I selected A2:A11 edit|copy then I selected C1 edit|paste special|check Transpose If all that works fine (and it did for me), then I deleted columns A:B and saved it as a new name. I bet it was just a minor mistake--try it again. If it doesn't work, keep track of what you did and where it went wrong and post back with your results. But I bet you'll be sending a "thank you" to Gord! Jimipolo wrote: many thanks for your time and effort in responding to my query. I;m not that great in excel and although your reply is 100% correct i cannot get it to work. maybe i am using an olser version of ecxcel as it doesn't give me the option to transpose certain labels - only a tick box! can i e-mail you my spreadsheet and you can talk me through it, or alternatively post a reply as if you were advising an absolute computer 1st timer in order that i will understand!! Once again i thank you for your assistance -- James "Gord Dibben" wrote: Jimi On a copy of the worksheet.......................... Assuming name is cell B1 enter in C1 and drag across to L1 and down until you get zeros. =OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0) When happy,select Columns C:L then copypaste special(in place)valuesOK Insert a row at row 1 then copypaste specialtranspose the headers in A1:A10 to C1:L1 Delete columns A and B. Gord Dibben Excel MVP On Mon, 25 Apr 2005 18:13:04 -0700, "Jimipolo" wrote: I have 22000 rows of data that are sorted into two columns. column a has 10 rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! |
#7
|
|||
|
|||
Gord,
I got it to work!!! I am a very happy man and cannot thank you enough, I have spent days on reformatting the data then realised that i couldn't do with it what i needed. I had to change your formula to *11 instead of *10 as i had an extra line in between each set of addresses. Once again thanks -- James "Jimipolo" wrote: many thanks for your time and effort in responding to my query. I;m not that great in excel and although your reply is 100% correct i cannot get it to work. maybe i am using an olser version of ecxcel as it doesn't give me the option to transpose certain labels - only a tick box! can i e-mail you my spreadsheet and you can talk me through it, or alternatively post a reply as if you were advising an absolute computer 1st timer in order that i will understand!! Once again i thank you for your assistance -- James "Gord Dibben" wrote: Jimi On a copy of the worksheet.......................... Assuming name is cell B1 enter in C1 and drag across to L1 and down until you get zeros. =OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0) When happy,select Columns C:L then copypaste special(in place)valuesOK Insert a row at row 1 then copypaste specialtranspose the headers in A1:A10 to C1:L1 Delete columns A and B. Gord Dibben Excel MVP On Mon, 25 Apr 2005 18:13:04 -0700, "Jimipolo" wrote: I have 22000 rows of data that are sorted into two columns. column a has 10 rows of address labels, a space, then repeated over and over again. i need to sort this information into 10 columns but don't know how. can anybody help with a formula that will enable me to have column headers of Name address etc and the data filtered correctly "current data" Name 1st Move International Ltd Add1 International House Worthy Road Add2 Chittening Industrial Estate Add3 Chittening Add4 Bristol PC BS11 0YB Tel 0117-982-8123 Fax 0117-982-2229 web www.shipit.co.uk I need it to be like this A B and so on Name Add1 and so on Please help!! |
#8
|
|||
|
|||
Good find.
You did mention in the first post you had a space between each set. Missed that. Gord On Thu, 28 Apr 2005 02:01:03 -0700, "Jimipolo" wrote: Gord, I got it to work!!! I am a very happy man and cannot thank you enough, I have spent days on reformatting the data then realised that i couldn't do with it what i needed. I had to change your formula to *11 instead of *10 as i had an extra line in between each set of addresses. Once again thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables filtering data | Excel Discussion (Misc queries) | |||
Filtering data from one worksheet based on another | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |