Share via

how to have a search filter that used a vstack for worksheet data consolidation? and return blank dates as blank not jan 00, 1900

Anonymous
2024-06-20T13:04:44+00:00

we have an old "CASE" excel database (thousands of data). now we need to filter them by the developer's name for a report that was requested.

the original excel file only contains certain rows

Case No. | File Name (Complainant vs Developer | Date Filed| Hearing date | Reset Date

now i want to include another column (formula) that get only the developer's name

=IF((B3<=0),"",(IF(ISBLANK(B3),"",(TRIM(MID(B3,SEARCH("vs ",B3)+LEN("vs "),255)))))) ---- this is working

our worksheets are divided by 500cases, and we have almost nearing to 7000

i am using a vstack to consolidate them in another sheet ("Central File"). but i can't figure how to create a search filter. filter them using the additional column "Developer's Name", with a cell address "B2"

=FILTER(VSTACK('0001-0500:0501-1000'!A3:F501),(VSTACK('0001-0500:0501-1000'!B3:B501)<>"")*(VSTACK('0001-0500:0501-1000'!F3:F501)=B2))

another thing, how can i return blank cells if Date Filed, Hearing Date, Reset Date, are blank, and not the jan 00, 1900?

Microsoft 365 and Office | Excel | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Rich~M 20,370 Reputation points Volunteer Moderator
2024-07-01T15:43:41+00:00

Hi MJRA.

Actually, you already have that functionality built into your formula. You are currently using a range of sheets so any sheet that is added in-between the first and last sheet of that reference will automatically be included in your formula.

I assume that you will want the sheets to continue to be in order by the Case Numbers, so you won't want one of the sheets to remain out of order to be the last sheet. You can add an end sheet that can actually be left empty. Its job will simply be to hold the end of the range of sheets. Here is an example. I named the holding sheet "TheEnd" and adjusted the formula to match in the screenshot below. Here is the revised formula.

=FILTER(VSTACK('0001-0500:TheEnd'!A3:F501),(VSTACK('0001-0500:TheEnd'!B3:B501)<>"")*(ISNUMBER(SEARCH(B2,VSTACK('0001-0500:TheEnd'!F3:F501),1))))

When you add a new sheet, drag the sheet tab to the correct spot to be in order between the first sheet and the last in your sheet range and the data from that sheet will automatically be included in the filter.

There will be some limitations on how many sheets or how much data can be processed this way. With only 500 lines per sheet the VSTACK max rows is not likely to be an issue. I don't know how many sheets you can successfully include with this process.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Rich~M 20,370 Reputation points Volunteer Moderator
2024-06-21T14:13:57+00:00

Hi MJRA.

I did some research and here is a modification to the formula that will search for the string in B2 in the Developer list in Column F and pull in anything that contains that string.

=FILTER(VSTACK('0001-0500:0501-1000'!A3:F501),(VSTACK('0001-0500:0501-1000'!B3:B501)<>"")*(ISNUMBER(SEARCH(B2,VSTACK('0001-0500:0501-1000'!F3:F501),1))))

If the string is found in more than one developer's name, it will pull in all of those developers.

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-06-20T19:59:47+00:00

    Hi MJRA. I am glad to help with your issue.

    The good news is that your formulas do work but need one adjustment. The reason that you are getting the #VALUE error in your FILTER function is because your first formula to pull out the developer's name is creating a value error when there is no developer name in Column B as in Row 5 of your 0001-0500 sheet as laid out in my sample below. This results in a value error in your second formula as well. You need to get rid of the value error in the first formula to pull the developer's name and then the other formula will work also.

    Use this on your data sheets with your formula wrapped in the IFERROR function to pull the developer's name into Column F but enter a blank if the name is missing.

    =IFERROR(IF((B3<=0),"",(IF(ISBLANK(B3),"",(TRIM(MID(B3,SEARCH("vs ",B3)+LEN("vs "),255)))))),"")

    Image

    With that fixed your FILTER function will work on your Central File sheet.

    Image

    In this case, the Filter function is pulling up this line because there is no developer in Column F or in B2. Putting a developer's name in B2 will pull up that developer's rows.

    Image

    To get rid of the Jan 00,1900 entries, on your Central File sheet open File>Options>Advanced. Scroll down to Display Options for this Worksheet and uncheck Show a zero in cells that have zero value. Now any cell with zero value will be blank as in E5 below.

    Image

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-06-30T03:41:35+00:00

    Thank You Very Much! It works!

    Can I try my luck and ask for an additional function? how 'bout for future that i need to add additional sheets, how can i call the sheet names automatically? that i dont need to modify the formula (Search) every single time i add sheets?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-06-20T23:31:56+00:00

    Hi! Thank You very much! it solved my problem. but, can i include an additional function or whatever it may call. that it can filter developer's name even i don't need to encode the whole developer's name?

    Thank You!

    Was this answer helpful?

    0 comments