Needing to copy data without blanks

[tbecker]

I have a worksheet that has 1900 lines some with data and some without. I want to copy the date from column d and the total from column R over to columns U and T. I am then hoping to filter with out the blanks and copy to another workbook. Colum R is a summation of columns k thru Q. I've tried linking and if statements but I can't get anything to work just right. Column d has dates and those are giving me fits as well with the 1900 date and i custom formatted to get them to 0, but still shows when I filter.

[AliGW]

Welcome to the forum.

As advised in the yellow banner at the top of this page, providing a sample workbook is a good idea.

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[TMS]

Maybe this:

Please Login or Register  to view this content.

If not, post a sample workbook.

[tbecker]

I have a spreadsheet in which I that data in columns A,B,C and I need to copy that info without the blanks. I tried the following formula, but keep getting the same line over and over

=IFERROR(INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW(2:2))),"")

[TMS]

With 365, you can use FILTER. Maybe this:

Please Login or Register  to view this content.

[tbecker]

The Let(hs,stack worked great. I need to make it stop at a certain row like row 8 thru 1357. When I do that I ger #N/A. Is that not possible

[AliGW]

Did you copy the formula correctly? If you did, it should limit the range automatically (because it is set to trim the ranges).

[tbecker]

Original formula before I limited it to stop at a certain cell =LET(hs,HSTACK(B8.:.B10000,R8.:.R10000),FILTER(hs,INDEX(hs,,1)<""))

Here is the formular after I limited it to stop at a certain line and I get #N/A =LET(hs,HSTACK(B8.:.B1357,R8.:.R1357),FILTER(hs,INDEX(hs,,1)<""))

[AliGW]

Without a workbook to see what's going on, it's nigh on impossible to diagnose. Please provide a sample workbook (desensitised).

[tbecker]

So here is a snip. I want the dates form column b and amounts from column r over to the right in T and U without any blanks. I need to separate the direct deposit from the scanned so I was thinking of doing scanned in w and x also without the blanks.

[AliGW]

For direct deposits:

=LET(hs,TAKE(HSTACK(B8.:.B8140,R8.:.R8140),MATCH("Scanned Deposits",A8.:.A8140,0)),FILTER(hs,INDEX(hs,,2)<>0))

For scanned deposits:

=LET(hs,DROP(HSTACK(B8.:.B8140,R8.:.R8140),MATCH("Scanned Deposits",A8.:.A8140,0)),FILTER(hs,INDEX(IFNA(hs,0),,1)<>0))

[AliGW]

The first formula could be this instead:

=LET(hs,TAKE(HSTACK(B8.:.B8140,R8.:.R8140),MATCH("Scanned Deposits",A8.:.A8140,0)),FILTER(hs,INDEX(IFNA(hs,0),,1)<>0))

[AliGW]

Oh, I see you've gone. Did it work for you? Some feedback would be nice.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[tbecker]

Is there a way for it to not grab the subtotals? there is no date in that column so I was hoping it would skip them.

[AliGW]

Did you see the second formula?

This:

=LET(hs,TAKE(HSTACK(B8.:.B8140,R8.:.R8140),MATCH("Scanned Deposits",A8.:.A8140,0)),FILTER(hs,INDEX(IFNA(hs,0),,1)<>0))

and this:

=LET(hs,DROP(HSTACK(B8.:.B8140,R8.:.R8140),MATCH("Scanned Deposits",A8.:.A8140,0)),FILTER(hs,INDEX(IFNA(hs,0),,1)<>0))

A 'thank you' would be nice ...

See post #13 which tells you how to sign off this thread.

[tbecker]

Sorry, I'm still working thru this. I am trying to put the info into my actual spreadhseet to see if it works since its 3,000 lines long. I will let you know for sure if I got it all the way. I do appreciate the help!

[AliGW]

Let me know either way and please sign off the thread.

It doesn't take much effort to say 'thanks'. It's the only 'payment' we get for our efforts and you're getting it all for free - please don't forget this.

[tbecker]

I have it figured out why it wasn't working. Someone has rows that are hidden for other types of deposits that I don't see or need the information on. That's why I'm having trouble. Should I just shorten my array?

[tbecker]

Thanks to everyone for their input!

[TMS]

You're welcome. Thanks for the rep.

[AliGW]

Lucky you! No rep for me.