sheet1 column to sheet2 row skipping every four and omitting said value

[chestersneakers7]

i have data going down sheet1 with no gaps and was able to pull from it and make said data go across a row on sheet2, skipping every four cells.
=IF(MOD(COLUMN(A1),4)=1, INDEX(Main!$A3:$A997, CEILING(COLUMN(A1)/4, 1)),"")
I need that same thing done again but if the data on sheet1 has a value of 1701 next to it, then I need that one omitted.

sheet1 a3 = sheet2 b2
sheet1 a4 = sheet2 f2
sheet1 a5 = sheet2 j2
sheet1 a6 = sheet2 n2

but if say sheet1 a4 had 1701 in cell h4, then I would not want sheet1 a4 on sheet2, it would skip to the next one that did not have a 1701 in the h column on sheet1.
I do hope this makes sense.

thanks guys/gals!

[benishiryo]

hi chestersneakers7, it's always better to have a sample Excel file. and by showing your existing formula but with a different eg, it's pretty confusing. not sure if my attached file is what you need. i did an array formula like this:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[chestersneakers7]

Oh this is awesome. Worked great. Only catch is the values from column a on sheet1 has letters and numbers in the cells. Is there a way to pull the information over if it has letters and numbers in it?

[chestersneakers7]

I uploaded the book you had put up and swapped out the sheet1 column a numbers with numbers and letters and all it will do on sheet2 now is carry over the first value from sheet1.

[benishiryo]

ok maybe this array instead in B2:

Please Login or Register  to view this content.

do mark this thread as "Solved" if that settles it. thanks

[chestersneakers7]

nice, thanks man!