Remove spaces

[Luigi802]

SO I have an excel sheet to track receipts pulled from a website, I figured out how to pull the raw DATA to get only what, problem is I have a bunch of "spaces" that aren't really spaces cause there's formulas in them, how do I get only the DATA without the spaces so I could copy/paste them all at once into another sheet? Please see attached XLSX sheet

[Pete_UK]

If by "spaces" you mean blank rows, then you can put this formula in, say, D2:

=IFERROR(INDEX(B:B,(ROWS($1:1)-1)*4 +2)&"","")

then copy down until your start to get blanks returned.

Hope this helps.

Pete

[Luigi802]

AWESOME, not sure how that formula works but it did, THANK YOU!

[Luigi802]

OK this is extremely frustrating, this work perfectly the first time I did it, then I tried it again after pasting in new info on the other sheet and all of a sudden it no longer worked???

[Pete_UK]

What do you mean by "it no longer worked"? Are you getting error messages, or just blanks? Does your first row of data appear on row 2, as it did in the sample file?

Pete

[Luigi802]

OK so This is the original sheet, the formula =IFERROR(INDEX(B:B,(ROWS($1:1)-1)*4 +2)&"","") worked perfect, until I pasted new DATA in the RAW tab, for some reason all of a sudden when I try this formula in the RAW tab or the other tab it doesn't seem to want to work, not sure what could possibly be the problem!

[Pete_UK]

In the original file that you attached the data was separated by the same number of blank rows each time, i.e. there were 3 blank rows between the data so the data appeared on every 4th row starting on row 2 - those are the numbers used in the formula that I gave you. However, in this latest file the separation between the data rows is variable. There are 8 rows between the 1st and 2nd and between the 2nd and 3rd record, but then it changes to 9 rows, then back to 8, and then every 4th row until it changes back to 8 rows at the end of your data. Consequently, you will need a different approach.

In cell D2 of the RAW sheet you can use this formula:

=IF(B2="","",MAX(D$1:D1)+1)

Then you can use this formula in E2:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$D:$D,0)),"")

and copy this into F2. All the formulae should automatically copy down to the bottom of your table. You can use the same formulae in the same cells in Sheet2 (though you will need to copy these down manually, as they are not part of the table).

Hope this helps.

Pete

[Luigi802]

Man you're good, thanks again! lol

[Pete_UK]

Glad to help - you might like to re-mark this thread as SOLVED, if you think it has been now.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete