Excel Magic Trick 1370 INDEX Function & Number Incrementor Extract Items & Display In Every 3rd Cell

Download Files:

Start File and Finished File:

See how to Create Formula to Extract Each Item In List & Display In Every Third Column. Topics:
1. (00:15) Introduction to Problem
2. (01:03) Formula Element Number Incrementor to create pattern like: 1,1,1,2,2,2,3,3,3… See functions COLUMNS and ROUNDUP
3. (04:40) INDEX Lookup Formula
4. (06:10) Formula Element Number Incrementor to create pattern like: 1,2,0,1,2,0,1,2,0… See functions COLUMNS and MOD Function.
5. (07:40) IF Function part of formula
6. (08:55) Summary
INDEX Function & Number Incrementor

[MohamedAlyCLAY]

hi dear mike
today i accepted in new vacancy in my job because i was #1 in excel in this vacancy
all of that by dint of your lessons
thanks to you very much.

[krn]

What a weird request. I agree with "Excel on Fire" ... Clever solution. Thanks Mike.

[OzduSoleilDATA]

Very unusual request, and clever solution.
It's got me thinking.

[maneshzaveri]

Interesting that there is always some solution in Excel ....but really not sure when I would use this.... would I take the easy way as you showed or really do the formula....of course will depends on the sample size... Always good to learn new stuff....Thanks Mike

[shau]

Hello Mike, thanks for another great tutorial. I have tried to use conditional formatting instead of the if formula without success. Please help. Thanks a lot

[BillSzysz]

Very elegant solution, Mike !!! Thanks for sharing.
If this is one-time job only, then i think we can use TEXTJOIN with a little trick in excel 2016 to get references like in your third row in "1370 (an)" sheet. (i mean =A1, empty cell, empty cell, =B1.... and so on in a proper cells). Unfortunately i can not to tell nothing more becouse i haven't got excel2016 so the solution is only in my mind :-)

[mohanpal]

Awesome trick
Sir make vedio rows number

[JohnVergaraD]

Actually you not need a Roundup function to do it.

Could be like:
=IF(MOD(COLUMNS($A3:A3), $H1)=1, INDEX($A1:$E1, 1+COLUMNS($A3:A3)/$H1), "")

Since Index get the int part of row or column number. Blessings! and keep going on with more interesting videos.

[alipezu]

Do you have some forum where we can ask questions and upload our excel workbook to get solutions for??

[nfranz]

hi.
my problem is I have thousands of file names listed in excel. I'm copying one by one and search from folder if do exist its .pdf / .doc / outlook file. If you can provide me a vba code says if cell A1(file name) is available in the folder, cell A2 " file exist" else "file not exist".

[TheMarissaCouch]

how can i make a budget that saves each file for each month and bundles a year for me?

[levelzeroD]

Maybe I can help with the draw my life vid if I come over to ur house

[shau]

The formula works perfectly, but the problem is how to format the cells to