I’m sorting a spreadsheet into which I pasted rows of data from a webpage but the data ends up as a single column on the spreadsheet instead of 7 columns. I would like a macro that starts at the left of the first cell, moves down one row and adds a cell, then moves down another row and adds two cells then moves down another row and adds three cells…until I get to the 6th row where I need to add 6 cells. The original webpage data has hundreds of rows so I would need the macro to continue down the page for 10 or 20 or more groups of data at a time. Once it’s done, I can just go to the bottom of the spreadsheet and even up all the rows by deleting the correct amount of cells from each column. Does that make sense? I can actually record the macro but I’m not able to make it repeat as needed and I’m not able to assign a hotkey to the macro. Any help, [image shows original column of data below and what I want to end up with above] please?
No kidding? Would you mind to explain for what reason such a splitting over rows might be purposeful?
In what way (by what character) are the entries separated in the source? Did you use the ‘Unformatted text’ option? Why didn’t an appropriate setting for import work? Before going macro you should consider many alternatives.
Can you give a link to an example from where you copied (and describe the range)?
(How do you delimit the groups automatically?) How can you be sure the “7-per-group” rule will persist in the long run? Please don’t post images, but give access to the “real thing” (spreadsheet document).
The data is coming from a chart on a web page. There are no separators–when copied and pasted to notepad (and then to the spreadsheet, the pasted data fills the cells straight down one column instead of across 7 columns as it exists on the original web page. I need the data to populate 7 columns in a row for each group of 7 pieces of data instead of going straight down in a single column so that the data is usable for the boss. Once I have the data in the format I’m requesting,
Once I have the data in the format I’m requesting, I can delete cells from the bottom of the data in each column and the rows of data will then line up correctly. I used to be able to do this in exel but just can’t figure out in libreoffice.
The way I used to do it was to start in the cell to the left of the uppermost cell w/ data, then drop down, add one cell, drop down again & add two cells, drop down and then add three cell, etc. My issue is that I’m not able to make the starting address relative nor can I get the macro to repeat all the way down to the bottom (even 20 at a time would be nice). Also, I’m not successful with creating a hotkey in order that I might manually run the macro for each group of 7 cel
No macro for purpopses of the kind!
If you feel sure to know better, please explain.
Thanks for your solutions though not quite what I’m looking for, I think. the following is a macro I recorded in about 30 seconds. It does what I need but only does it for a single set of 7 cells (vs. doing it for a whole column of cells --7 at a time). All I need is to make this macro repeat down the column any number of times each time it finished with the previous 7 cells. What’s the best way to upload code so you can see it?
To get a recorded macro to actually do what you want, you mostly will need much more time than to record it, and particularly knowledge.
This knowledge isn’t of the kind you can get the way you tried.
As the request is highly specialised, I see only two ways:
-1- Write a macro for you though you didn’t explain but simply state the need.
-2- Hint how to solve this task and similar ones by proper spreadsheet means which not is macros…
Id did -2- and won’t do -1-.
Sorry.
One of many thinkable suggestions is demonstratd in this attached spreadsheet document document.
Quoting @Cjenkins from his first comment on the question:
“I need the data to populate 7 columns in a row for each group of 7 pieces of data …”
Well that sound roughly reasonable, and is a reoccurring task in spreadsheets.
Though you oviously didn’t check my first attachment, I offer this second attachment here giving a solution to the more reasonable variant of the task, too.
I know that MS encourrage users to rely on user code in VBA-Excel. There are more sound solutions as you may see checking my attachments.