LinkBack URL
About LinkBacks
Dear members of the forum!
Is there a command to copy each entry of a row into another row but leaving blacks in between?
I made a screenshot to show how it should look like.
Thank you in advance,
David
Dear members of the forum!
Is there a command to copy each entry of a row into another row but leaving blacks in between?
I made a screenshot to show how it should look like.
Thank you in advance,
David
I hope its fine, that I just sent an image instead of the table.
If needed I can upload the file as well
....yes please!If needed I can upload the file as well
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Here we go:
it is in row 58-68
Last edited by mrdaave; 10-06-2022 at 06:55 AM.
Ignore this
Pete
Me or John?
I could not find a suitable solution for this problem...
I would really appreciate, if someone knows a formula for this
im M59
=IF(MOD((ROWS($1:1)-1),3)=0,INDEX($D$59:$D$62,INT((ROWS($1:1)-1)/3)+1),"")
Copy down
in O59
=IF($M59="","",VLOOKUP($M59,$D$59:$K$62,COLUMN(C$1),0))
Copy across and down
Replace "," with ":"
I have already tried this with a formula including ROW, INDEX and OFFSET - but somehow I just cant get the right solution...
Thank you John - I will try this right now
Delete ALL expected results.
=LET(d,D59:K62,gap,3,rd,ROWS(d),cd,COLUMNS(d),IF(1+MOD(SEQUENCE(rd*gap)-1,gap)=1,INDEX(D59:K62,1+INT(SEQUENCE(rd*gap)/gap),SEQUENCE(,cd)),""))
format the result area as :
0;-0;;@
to hide the zeros in the second column.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
On second thoughts... Go with JT's. If you enter anything else, anywhere in the results area, my formula will return a #SPILL error.
@John: somehow I was not able to use your formula - I cannot get any results
@Glenn: That looks great! how can I format the result area?
Anyway I would like to thank both of you for your fast help!
Okay - Ill try again.
That`s important, because in the second step, I would like to use a second datapool and fill every second row underneath the name with the same formula
Post a sample sheet showing ALL relevant datasets.
This should be the final result...
Its a complex thing for me - but its difficult to change the original data in a form to easy work with (almost 30k entries)
See attached
Thank you John!
This is now working
Unfortunately I run into the problem that I cannot use a formula for the second row, because this will overwrite the already calculated results.
I am sorry, that I didnt post the whole problem in the first place, but i thought, that I can just use the same formula three times...
A dynamic array.
Formula:
Please Login or Register to view this content.
Ensure that there is NOTHING in the cells where the results are going to spill out.
You will need to copy/paste to every 4th row OR use a VBA solution to put the formula in the relevant cells.
Thank you Glen for your help - i will try to match this to the original Data. I hope that I can manage to write the formula for the big data sheet
@John: Is it difficult to write a VBA solution for this problem? I am really new to programming - so I am not sure if i can handle that for the original data sheet...
99.9% of the effort there was filling the blank rows in column D. With a VERY minor adjustment it simplifies RADICALLY to:
=SORT(VSTACK(C30:J33,C38:J41,C46:J49),1,1)
If you can live with the change, it is sooooo easy.
Even if you add the categories in an additional column to one side of each group... e.g. after July... it can be done easily....
Like this:
=SORT(CHOOSECOLS(VSTACK(C30:K33,C38:K41,C46:K49),1,9,3,4,5,6,7,8),1,1)
wow - thats no problem, i can do that easily!
I will try this solution - the other one is quite difficult to match...
Okay, I`ve tried this solution but unfortunately I ran into the problem that Excel shows "_xlfn." and as far as I found out, this formula is not working with my Excel-version.
1. Go to File /Account/About Excel. What Version number do you have?
2. Can you check for upgrades?
3. Have you enabled editing on ALL of the versions I have produced? If so , which version was the LAST one to work AFTER editing was enabled.
Hopefully CHOOSECOLS is the problem and we can work around that.
Microsoft 365 MSO (Version 2202 Build) 64 Bit
Updates are done properly from the business-administrator - and the product is quite new (max 1,5 months)
With the dynamic array it stopped working. And I just tried to implement the formula to the original data - thats where I found out, that something is wrong.
Editing should be enabled.
Please answer Q3 CLEARLY.
I suspect that it'll only work easily with 2203 or later.
On your version of Excel is VSTACK available? yes or no...
I do have a workaround... but I need to know how far back I need to go to ensure compatability....
Hello.... RU out there....
One more Q. Are you OK with the categories being copied down in row D, or do you need them off to the side?
Q3: All of the the versions have editing enabled. Last version which worked was the first one (dynamic array doesnt work)
I cannot find the VSTACK formula
Categories can be in row D - that does not matter at all
Please answer Q at Post 30.
OK. Sorted. It'll take me about 10 mins....
Thank you so much Glen!
You`re amazing
Yet another final Q.... In your real sheet, are the 3 data blocks all on the one sheet... or 3 separate ones....
If all raw data on one sheet - where is the result - same sheet or different?
If all raw data on separate sheets, where is the result - on a 4th sheet??
OK.
Enable editing BEFORE replying.
=LET(a,C30:J33,b,C38:J41,c,C46:J49,ra,ROWS(a),rb,ROWS(b),rc, ROWS(c),x,SEQUENCE(ra+rb+rc),F,INDEX((a,b,c),IF(x>ra+rb,x-(ra+rb),IF(x>ra,x-ra,x)),SEQUENCE(,COLUMNS(a)),IF(x>(ra+rb),2+(x>(ra+rb)),1+(x>ra))),SORT(F,1,1))
It looks scary, but it's not. If it works for you... fine. If not I'm off to drown my sorrows in the nearest pub.
If it works OK in the sample, paste it FROM THE FILE into your real data. Make sure there's space for the results to spill out.
The ONLY bits you need to adjust are the bits in RED. The rest should pretty much look after itself.
For your final question: raw data all on on sheet and result also on the same sheet (messy but easier to handle
I will just try to match it with the real data.
So far thank you very much for your effort - if it works or not, im just thankful!
It works!
Thats just amazing - this will save me so much time in the future, thank you so much
I think you can go to a pub anyway, I will do so too - and drink one on you as well!
Thanks again Glen!
Woo Hoo!!
I never go to the pub before 18:00.... BUT now there's only 51 minutes to wait... If I was in Greece, it would be 18:00 already
Same for me - but now I can finish work for today and go home (and to a pub to get a good Whiskey)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks