Pergunte aqui

Perfil de p - atividade

2017-12-27 11:21:13 +0200 Medalha recebida  Estudante (fonte)
2017-05-03 17:32:20 +0200 Medalha recebida  Pergunta famosa (fonte)
2016-08-27 17:53:22 +0200 Medalha recebida  Pergunta popular (fonte)
2016-08-27 17:53:22 +0200 Medalha recebida  Pergunta notável (fonte)
2016-04-16 04:40:07 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

SIDE NOTE Where is the checkbox to signup to email alerts when a new comment is added so one doesn't have to open bookmarks to find this post. i dont need "subscribe to rss feed" i need to get an email that someone posted. thank you

2016-04-16 04:36:24 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

This topic remains partially answered to anyone that would like to contribute, it's much appreciated, hopefully programmers take input in forums and integrate it right away with new versions however with spreadsheet software being around for decades it seems as though it's direction may be more focused on design than implementation. Spreadsheets should be able to do more advanced functions with sorting, filtering and rearranging data out of the box than just it's basic operations and functions.

2016-04-16 04:30:13 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

I tried to follow the instructions you gave as well as I could comprehend and also tried various others methods. I didn't think that this should be so difficult but I guess it is as this answer is only partially correct and no one else has commented. It's disappointing that spreadsheet software these days isn't intelligent enough to rearrange data with a couple of clicks. Maybe I'm using the wrong program but I thought Calc would be able to do this in a blink of an eye. Thanks for trying :)

2016-04-16 04:26:13 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Thank you Lupp unfortunately the ask68180ComplicatedRearrangement001.ods engine does not work further than line 23 then stops. This may be due to Column A adjacent cells changing (there is no define pattern that i saw such as 6 rows of adjacent data 4 rows of empty rows 7 rows of adjacent data then 2 rows of empty rows and repeating until the column is exhausted of entries. I've tried several ways to edit the engine, without knowing where to start it's a dead end.

2016-04-15 06:09:00 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

I've tried various of things and still nothing is working either because I'm doing something wrong or not quite understanding the steps exactly as I need to do. If there was a video somewhere would probably solve this immediately but I've googled and checked youtube and came out short on a solution.

It won't let me change anything, don't know where to start. Some cells managed to fill with something Err:508. Am I supposed to delete everything in the purple box? Thank you

2016-04-15 05:49:29 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

am i missing a step and supposed to be doing something with Column G and/or R (GENERATOR_GROUPS) because from what I've tried isn't working.

I'm using a mac so when i press Control a popup appears so I guess it would be Command button.

Anyways, E25 and all the way down says #N/A maybe something broke, I can't alter anything in the Array (purple cells)

should've i done the helpers first then put my data in so the array cells would recognize it, im lost. is there an animated help gif or video?

2016-04-15 05:16:50 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

step 1 done step 2 copied from C to F all the way down to the last line in Column A, i noticed lowercase "a" (without the "quotes") in Cell didn't include Column G14 to G207 but did not include Column G while dragging the Helper columns (C through F) all the way down to Row 25960. Step 3 I don't know what cell I'm starting from...I keep getting a popup that says "You cannot change only part of an array."

2016-04-14 21:54:15 +0200 Medalha recebida  Pergunta famosa (fonte)
2016-04-14 21:00:15 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively
  1. Fill down the formulae in the helper columns as far as data are present in column A.

Do I continue where you left off and Left Click in C207 then drag right to cell F207 or G207?

OR

Do I start with a Left Click in C2 then drag right to F2 or G2?

THEN

from which cell do I drag down all the way until row 25960?

thank you

2016-04-14 20:50:03 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

I am going to use your instructions and step by step write how I follow them

1. Fill in your 1-column-data into column A of sheet 'Solution' beginning with A2.

If my data starts at A1 (w/o without a single header row) the last filled cell entry ends at A25959. If it starts at A2 (with a single header row) then the last filled cell entry ends at A25960

I left clicked on A2 to select the cell then scrolled down using the scroll bars and shift left clicked on cell A25960

Copied, Pasted

2016-04-14 19:17:22 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

i thought there may be some quick easy macro script i could run that would crawl the columns transposing all contiguous/linear grouped data sets, ignore any/all blank/empty rows then to loop and keep repeating itself until it intelligently finds no more data in source column OR reached spreadsheet maximum row limit OR certain amount of empty rows OR specified amount of rows

2016-04-14 19:13:33 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

the way i received the data is bad i know it's horribly organized which makes it impossible to compare and sort unless the rows are transposed to like/similar columns for sorting purposes.

2016-04-14 19:13:33 +0200 Medalha recebida  Palpiteiro
2016-04-14 18:30:58 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

i posted another question if there is a way to remove all empty rows except for one. then maybe i could find a macro or someone what could make a formula that transposes/moves every block of linear/contiguous set of grouped data to the new columns.

I'm was hoping for the easiest quickest solution. i don't even need to keep formatting or cell equations just the alphanumeric characters, numbers & symbols in the cells.

i would like to know how to do this myself if possible too. any videos? ty

2016-04-14 08:32:11 +0200 Perguntou How to remove multiple empty rows between grouped sets of data (arrays ranges) but leave only one row line empty between each one

How to remove multiple empty rows between grouped sets of data (arrays ranges) but leave only one row line empty between each one?

Multiple ranges are contiguous in rows of data and would like to remove all the extra lines (anything more than one and only preserve one).

How to accomplish this task with:

Question 1) multiple ranges in only one column?

Q2) multiple ranges in more than one column?

How would you keep two (or specify more) blank rows between each set of data?

KEYWORDS TAGS calc rows empty blank spaces remove delete edit

2016-04-14 04:06:27 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

I know it may seem as though transposing this data would not help but to a noob it really does for comparison sorting and aesthetics on screen. I'm sure more advanced users have skills to do this and much more but I just need it so I can eventually sort the results in columns as needed. You are a big help and very much appreciated

2016-04-14 03:54:51 +0200 Medalha recebida  Pergunta notável (fonte)
2016-04-14 03:33:15 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Would it help if I found out exactly how many rows my source Column A has in this project? Thank you for all your help!

2016-04-14 03:29:29 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

I copied my Column A into your ask68180ComplicatedRearrangement001.ods and it works! :) but it only does it for 23 rows :( I guess those Helper columns you added actually do something :)

Is there a way to make this work for 20,000+ rows? since i will be given more data on spreadsheets like this in the future, a rearrangement with more or less rows to insert my source column and specify the amount of rows needed or make it infinite which would be preferred method so dont have to do it manually

2016-04-14 01:59:38 +0200 Resposta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Lupp, I think the example you posted is what I need. Your example shows Column A which I call the source and it shows the array/ranges which I call "sets" grouped together then transposed to Columns H through N. I don't need my results to show Columns C through G including the Helpers which I don't know what they are).

How do I implement this to my workbook spreadsheet to reflect upon 20,000+ rows of data in Column A which contains many sets grouped together? Thank you very much!

2016-04-14 01:47:38 +0200 Pergunta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Sorry forgot to mention that the code I quoted above in the link worked when I removed all blank rows from the Source/Original Column and it was used as an example. The formula to make this work may have to be done from scratch to ignore/omit the empty rows/lines in the Source Column to place respective data in the Transposed Columns

2016-04-14 01:17:59 +0200 Medalha recebida  Pergunta popular (fonte)
2016-04-14 00:58:00 +0200 Pergunta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

tried to copy & paste example from spreadsheet into this comment box but it came out screwed up so here is a link to a screenshot, hope it helps and sorry if i'm using the wrong terminology and if so please feel free to correct me so i know. thank you!

http://www.ge.tt/3tOpBeZ2

2016-04-14 00:16:54 +0200 Pergunta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

the formula i found & modified a bit actually worked well until it got to several rows down then shifted the results cell 1 (one) column to the right because the original/source (hope i'm using this term correctly) contained a group of data (they are about 7 rows max from what i saw but could be more or less somewhere buried in 20,000+ rows of data). i would like to visualize this so will add a comment trying to structure this hopefully it comes out, if not will try to attach or link an image

2016-04-14 00:09:51 +0200 Pergunta comentada Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Hello Lupp thank you so much for replying quickly, I may have used the wrong terminology or need to explain myself a bit better.

the spreadsheet contains 1 column, in that column there are groups of data (some are 6 rows some are 7 rows from what ive seen could be more could be less). each group is separated by 2 or 4 empty rows. what i need is to take each group in the original column and have them transposed into columns which would total 6 or 7 columns then start over for each set of data

2016-04-13 21:20:11 +0200 Perguntou Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Need to transpose many one (1) column arrays/ranges/groups/sets multiple mini tables consisting of varying contiguous rows to several columns respectively omitting empty rows but including extra column in transposed results if original column set contained extra row

Spreadsheet contains one (1) column starting at A2 with over 20,000+ (twenty thousand) rows which needs to be transposed preserving existing order in multiple columns

If the data in ranges was consistent (every 6 cells) a quick modification of the solution in the following example would work

=IF(INDEX($A$1:$A$17328,ROW(A1)6-6+COLUMN(A1))=0,"",INDEX($A$1:$A$17328,ROW(A1)6-6+COLUMN(A1)))

SOURCE(S)

http://www.excelforum.com/showthread....

http://webcache.googleusercontent.com...

HOWEVER

from quick observation the mini table arrays are grouped in varying sets of 6 (six) or 7 (seven) COULD BE MORE OR LESS AND THERE IS NO CONSISTENT PATTERN contiguous row ranges separated by various empty rows which can be four (4) or two (2) rows COULD BE MORE OR LESS AND THERE IS NO CONSISTENT PATTERN and using the above formula works fine up to the point that the original data in the 1st column contained an extra row such as Set1Data7 (again this could be MORE OR LESS and would need to include all grouped data separated by a space)

keywords tags calc transpose varying multiple rows array range column between empty