Transposing columns into new rows.
Hello,
I am running into some trouble trying to convert my data into a usable format. I have a site name in column A followed by IP addresses in columns afterwards ranging from 1 to hundreds of columns. I need this data to be transposed onto new rows instead of columns. I am having a hard time explaining my end goal but have attached a example of what I am trying to accomplish. any help would be greatly appreciated.
Last edited by ensio0651; 03-04-2019 at 01:17 PM.
Hi & welcome to the board.
How about
Please Login or Register to view this content.
Try:
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Apologies not the most competent when it comes to running code in excel could you tell me how to use the code.
Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.
Have a look here https://www.contextures.com/xlvba01.html
The code needs to go in a regular module.
Also with my code you will need to create a new sheet called Output
Thank you I got it working you are a life saver!
You can do it with a few formulae, as the attached file demonstrates, using these formulae in the cells stated on the Desired sheet:
A2: =COUNTA(Current!B2:Y2)
B2: =A2+B1
It is imporatant to put zero in cell B1. The first formula gives a count of the number of IP addresses in the Current sheet - you might need to change the Y to some other column, sufficient to cover the maximum number of IP addresses that you have. The second formula gives a cumulative count of the numbers.
Copy these two formulae down until you start to get zeros in column A (this depends on how many entries you have in the Current sheet). Then put these formulae in the cells stated:
D2: =IF(ROWS($1:1)>MAX(B:B),"-",MATCH(ROWS($1:1)-1,B:B)+1)
E2: =IF(OR($D2="",$D2="-"),"",INDEX(Current!A:A,$D2))
F2: =IF(OR($D2="",$D2="-"),"",INDEX(Current!B:Y,$D2,COUNTIF(D$2:D2,D2)))
Again, you might need to change the Y parameter, as advised above. Copy these three formulae down until you start to get hyphens in column D.
Hope this helps.
Pete
Glad we could help & thanks for the feedbackOriginally Posted by ensio0651
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks