/sci/ - Science & Math » Thread #13620230

43KiB, 480x448, 54563476437.jpg

View Same Google iqdb SauceNAO

Anonymous Thu 09 Sep 04:23:04 2021 No.13620230 View Reply Original Report

Quoted By: >>13620233 >>13620249 >>13620254

How do I make this work in Excel?
Want to put 1,2,3,4,5,6,7,8,9,10 at the E column

Anonymous

Anonymous Thu 09 Sep 2021 04:25:59 No.13620231 Report

Quoted By:

Forgot to say that I want to put it in a way so that i can Autofill down in the E column

Anonymous

Anonymous Thu 09 Sep 2021 04:28:52 No.13620233 Report

Quoted By: >>13620235

>>13620230
don't quote your formulas, your computer thinks you are writing a novel, not a program statment

Anonymous

Anonymous Thu 09 Sep 2021 04:30:17 No.13620235 Report

Quoted By:

>>13620233
i know dude lol
didnt know how to explain, put it in quotes to explain what cell i wanted to be in the output

Anonymous

Anonymous Thu 09 Sep 2021 04:36:19 No.13620241 Report

Quoted By: >>13620256

the operation you're trying to accomplish is called flatten. i don't use excel, so i can't tell you how to do it, but that's what you need to be googling.

Anonymous

View Same Google iqdb SauceNAO index.png, 10KiB, 536x354

Anonymous Thu 09 Sep 2021 04:38:01 No.13620245 Report

Quoted By: >>13620255 >>13620256

Best I can do

For some reason it wants to return two cells

Anonymous

Anonymous Thu 09 Sep 2021 04:39:12 No.13620249 Report

Quoted By: >>13620252

>>13620230
Use VBA to iterate over over your rows and then your columns. Map that output to a single column.

Anonymous

Anonymous Thu 09 Sep 2021 04:41:16 No.13620252 Report

Quoted By:

>>13620249
Alternatively, do a pivot table.

https://www.listendata.com/2015/02/excel-formula-convert-data-from-wide-to.html

Anonymous

Anonymous Thu 09 Sep 2021 04:43:11 No.13620254 Report

Quoted By:

>>13620230
Just so we understand, the numbers are just an example and you want it work with any data, right?

Anonymous

Anonymous Thu 09 Sep 2021 04:43:20 No.13620255 Report

Quoted By:

>>13620245
nvm, change the formula to

=INDEX($A$4:$B$8,CEILING(D4/2,1),MOD(D4-1,2)+1,1)

in the first cell

you still need to fill some cells with the numbers 1 to 10 so the autofill knows what you mean

Anonymous

Anonymous Thu 09 Sep 2021 04:43:56 No.13620256 Report

Quoted By:

>>13620245
thnx for trying though
this shit is mind-boggling to me, looks so damn simple and yet i just cant find a way to do it
>>13620241
doesnt really work cause i want it to be dynamic

Anonymous

View Same Google iqdb SauceNAO index.png, 10KiB, 641x261

Anonymous Thu 09 Sep 2021 04:49:12 No.13620262 Report

Quoted By: >>13620265 >>13620281

hopefully this makes sense

need to have the numbers 1 thru N in column D (in this case). then paste that formula in the first cell below it and dragging should work

Anonymous

Anonymous Thu 09 Sep 2021 04:50:51 No.13620265 Report

Quoted By:

>>13620262
for a more general case, replace both of the 2's in the CEILING and MOD functions with the number of columns (or count the number of columns to make it more dynamic).

Anonymous

Anonymous Thu 09 Sep 2021 04:58:25 No.13620281 Report

Quoted By:

>>13620262
it worked!! Thanks so much dude, you have no idea how much time i spent on this lol

Capcode	All Only User Posts Only Moderator Posts Only Admin Posts Only Developer Posts
Show Posts	All Only With Images Only Without Images
Deleted Posts	All Only Deleted Posts Only Non-Deleted Posts
Ghost Posts	All Only Ghost Posts Only Non-Ghost Posts
Post Type	All Only Sticky Threads Only Opening Posts Only Reply Posts
Results	All Grouped By Threads
Order	Latest Posts First Oldest Posts First

Your latest searches