Hello
Please find a sample spreadsheet attached. I have data (Name, Address, Phone, Web) all in a single column but I want to have them transposed into their individual columns so that I can sort/ filter the data with those
There are a few thousand sets of such data that I need transposed
Kindly advise
Many thanks
If you have list of cities in separate sheet then it will be done, why because some of the cities have more than 1 word and there is not column delimiter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi Siva
Thanks for your reply. I understand that the previous sheet didn't have a delimiter before the city to help with the tranpose.
Could you kindly look at the following spreadsheet and advise if it could be done with this one?
Many thanks
This proposed solution uses formulas which start in the same row as the first line of data.
The formula for column B is: =IF(AND(A2="",A3<>""),A3,"")
The formula for column C is: =IF(B3<>"",A4,"")
The formula for column D is: =IF(AND(B3<>"",A6<>""),A5,"")
The formula for column E is: =IF(AND(B3<>"",D3<>""),LEFT(A6,SEARCH(",",A6)-1),IF(AND(B3<>"",D3=""),LEFT(A5,SEARCH(",",A5)-1),""))
The formula for column F is: =IF(AND(B3<>"",D3<>""),MID(A6,SEARCH(",",A6)+2,2),IF(AND(B3<>"",D3=""),MID(A5,SEARCH(",",A5)+2,2),""))
The formula for column G is: =IF(AND(B3<>"",D3<>""),VALUE(RIGHT(A6,5)),IF(AND(B3<>"",D3=""),VALUE(RIGHT(A5,5)),""))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you so much for the formulaOriginally Posted by JeteMc
However, when working on the ZIP column, I am seeing some of the ZIP codes being stripped off to the last 1-2 digits
Kindly refer to the attached spreadsheet to see what I mean
Thanks once again for your help
You have a space at the end of some of the rows that contain the zip code - you should use the TRIM function to get rid of them, like this, amending JeteMc's last formula:
=IF(AND(B3<>"",D3<>""),VALUE(RIGHT(TRIM(A6),5)),IF(AND(B3<>"",D3=""),VALUE(RIGHT(TRIM(A5),5)),""))The formula for column G is:
Hope this helps.
Pete
B3=IF(AND($A2="",$A3<>""),$A3,"")
C3=IF(AND($A2="",$A3<>""),$A4,"")
D3=IF(AND($A2="",$A3<>""),IF(INDEX(A3:A18,4)="","",$A5),"")
E3=IF(AND($A2="",$A3<>""),TRIM(LEFT(IF(D3="",$A5,$A6),SEARCH(",",IF(D3="",$A5,$A6))-1)),"")
F3=IF(AND($A2="",$A3<>""),TRIM(LEFT(SUBSTITUTE(TRIM(MID(IF(D3="",$A5,$A6),SEARCH(",",IF(D3="",$A5,$A6))+1,256))," ",REPT(" ",20)),20)),"")
G3=IF(AND($A2="",$A3<>""),TRIM(RIGHT(SUBSTITUTE(TRIM(MID(IF(D3="",$A5,$A6),SEARCH(",",IF(D3="",$A5,$A6))+1,256))," ",REPT(" ",20)),20)),"")
tRY THIS AND COPY TOWARDS DOWN
SEE ATTACHED FILE
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks