Can you please help me optimize my formulas for CSV export? I use Filter formula and Indirect Formulas

[chloec]

Hello Excel Experts and Wonderful Wizards,
I have an excel workbook attached here that "works" but the formulas need to be optimized, with your kind help.

The purpose of the workbook is save the Import Tabs as a CSV, but the way the workbook is set up, I have helper columns that I wish to exclude from CSV. I also have to use a combo of Filter + custom formula in SAME ROW, because I cannot "index" something >256 characters. It's clunky.

Workbook:
1 Data Tab = "Stories"
2 Import Tabs - "ImportT1" & "ImportJ1" - these tabs need to be saved as two CSV files, so I can import them into another program.

On the import Tabs, I have made a number of suggestions and queries - hope you can check them out and provide me with feedback!
Thank you, wizards, gurus!

Example Questions:
1 - Notice that Colums H - J do NOT use the Filter formula, because when I ask Excel to return data from Stories Tab, we max out at 256 characters with the filter formula, so I had to create an alternative formula! Any other solutions?
2 - This TAB needs to be saved as CSV so I can import it into another program…but I do NOT want Column A (Col A is Used only to obtain the ROW so I can get data in Columns H - J). I have tried to HIDE column A but row data appears when I save file as CSV. Col A is really only a helper column for Cols H - J
3 - I have to keep pulling down formula in Columns H - J -- is there any way we can create a formula that goes as far down as the data then stops
4 - Seems like my formulas are too big and complicated - can they be simplified?

CSV Issues
When I save as CSV - I do NOT want data from Column A, I also do not want a bunch of commans ,,,,,,,,,,,saved as CSV from pulling down the formula in Cols H - J

 

[BobBridges]

No one else seems to be making a stab at this, so I'll see what I can do. But I think I'll only help partially.

1) "Notice that Colums H - J do NOT use the Filter formula, because when I ask Excel to return data from Stories Tab, we max out at 256 characters with the filter formula, so I had to create an alternative formula! Any other solutions?" I never use the FILTER function, but I looked it up and the second thing I notice is that it's available only with Excel 365, which I don't use (and hope I never will). So I can't help you there—except that my answer to some of the other questions may give you ideas about it.

2) "This TAB needs to be saved as CSV so I can import it into another program…but I do NOT want Column A (Col A is Used only to obtain the ROW so I can get data in Columns H - J). I have tried to HIDE column A but row data appears when I save file as CSV. Col A is really only a helper column for Cols H - J". Here's the thing: A CSV has no internal formatting—it's plain text. So fonts, colors, borders, hidden rows and columns, even formulae, all are lost when you save an .xlsx as .csv; all you save are plain-text values and that's all you get back when you reopen it. You cannot get around this when you save to a CSV. (That's why you can't have multiple worksheets in a CSV, too.)

There is a way to automatically delete the helping columns before saving it as a CSV; see below.

3) "I have to keep pulling down formula in Columns H - J -- is there any way we can create a formula that goes as far down as the data then stops" I don't think I understand this question; we'll have to talk about it some more.

4) "Seems like my formulas are too big and complicated - can they be simplified?" Could be, but we should tackle them one at a time. And what I say next may be the best approach.

If you're open to writing code in the VBA programming language, there are things you can do to automate much of what you want to do. Your program can delete the helping columns before saving one of the worksheets as a CSV. It's probable that a program can figure out where the end of the data is and fill in the formulae to that location. And I have written many of my own worksheet functions that simplify the formulae in the worksheet. Now, granted, I like programming, and if you've tried it and don't like it, or are merely terrified at the thought and are unwilling to try it, this won't be much help to you. But it seems to me that much of what you're trying to do is better done in VBA/Excel. The nice thing about that is that you can do virtually anything automatically...though it's some work getting there the first time.

 

[chloec]

Bob Bridges, thank you for being a rock star and trying to tackle this. I'll review your feedback soon. Just want to hop on to say thanks!!

 

[BobBridges]

LOL, now I have to post so I'll see your followup. I'm not completely sure, but I think, I think, that when someone posts a reply to a thread and it sends me an email about it, it won't send me any more emails about subsequent replies. So once you've reviewed my thoughts and reply again, it probably wouldn't send me an email about it and I'd never notice.

I appreciate the appreciation, though .

 

[Marc L]

Hi !​

No one else seems to be making a stab at this

So weird to ask help for formulas in the VBA forum section instead of the appropriate section !​

​

As for a classic VBA solution that just needs a workbook attachment containing the source raw data worksheet only​

- should get rid of any additional useless worksheet -​

and according to this source data worksheet an attachment for each expected result text file without any error​

with a crystal clear complete elaboration for each non raw data (transformed) …​

​

 

[BobBridges]

...a crystal clear complete elaboration...

I no longer bother to be puzzled and exasperated when folks who need help cannot or will not describe their problem in detail without first being dragged to it by detailed questions. I suppose if they could think about it clearly they'd already have figured it out themselves. I've been a technogeek for decades now, and I'm just resigned to it.

I once had a user at a truck manufacturer who called me one day to complain "You know that trick you showed me the other day? Well, it didn't work." He then paused, waiting for me to make a diagnosis.

"What did it do?", I asked.

(You know what he replied, of course.) "Nothing."

"Oh, come on, it did something. Was there an error message? What did it say?"

"Oh, it said some damn thing", he half-snarled. He was laughing at himself as he said it, because he knew what I was going to snarl back, but the fact remains that he hadn't read the error message.

 

[Marc L]

Yes I assume the folk must well describe his need when creating a thread on any Excel forum.​

That avoids to waste my time and to trash my original VBA code when the folk indicates later « I forgot to explain in fact I need … »​

The reason why when I share some help I often start with « According to your initial post / attachment a VBA demonstration for starters »​

meaning if anything is changed, misexplained or forgotten the folk should fix it himself.​

Before to post any VBA demonstration I wait until all lights are green, like when launching a space ship.​

​

In another forum a folk was warned if he still continue to create initial post under the level of what any Excel forum expects for,​

he will not be surprised if he won't get any answer. That just happens. As he made no effort, no try, to reuse any advanced solution​

he got from several helpers. After a week I proposed to share a code like any Excel user operating manually,​

so far easier for him to maintain as he often changes the rules, but slower.​

But as he do not want 'slower' so he's still waiting …​

​

As here some of the worksheet functions used in the Import worksheets do not exist in my tests laptop old Excel version,​

as when using VBA to export data that no needs such additional Import worksheets,​

as it is not difficult to share the exact expected result csv files like to explain the rules to follow for each field …​

For what I can foresee the VBA procedure should be short (less than 30 codelines), instant result for both csv files created at once.​

​

Anyway if chloec wanna formulas so this thread should be moved to the appropriate forum section.​

​

 

[chloec]

Hi Folks.

I think someone (not me) moved my thread into a VBA forum incorrectly thinking I wanted VBA help (see screenshot), but I still stand behind my statement.. I need formula help. I have to research how to move it back. Thanks for all the posts and feelings though, it must be really challenging to moderate and provide input - but I'm so glad for all the helpers on these forums! Thanks!

 

[Marc L]

Before to move this thread to the Excel formulas section :​

once you have the correct results with the formulas you will have to manually replace the formulas with their values,​

delete the useless column A then copy the worksheet as a new workbook then save this new workbook as a csv file …​

So is it really what you want rather than a direct automatic VBA export ?!​

​

 

[chloec]

Hi @Marc L - thank you!
1) I don't use VBA so wasn't prepared to ask/accept help with VBA. I could follow specific directions and steps but I thought an formula wizard would know how to answer my post using a formula. However, it took a long time for my query to get any attention in the Excel "formula" forum until someone moved it to VBA - and maybe that is because there is no way to execute via formula! :-(
2) I was hoping someone would know how to incorporate my "helper A" column into the formula, so It would not appear when I export to CSV, so that I wouldn't have to do all the steps that you outlined!

Hope everybody is having the best day.

 

[BobBridges]

Chloec, the reason we're talking about VBA instead of formulae is that I really think some of what you want simply cannot be done with formulae. I get that you don't want to dig into VBA. But no formula will, for example, save a worbook for you, or delete a column, or hide it. I shouldn't try to answer for Marc; he's much better with advanced Excel functions that I am. But I'm sure of my answer. A CSV will never save formulae or keep a hidden columns hidden. And if you really want to automatically delete a column and then save one of the worksheets as a CSV, you gotta go to programming.

Marc may be more inclined to write a program for you (once you can define exactly what you need), and I'm more likely to teach you how to write it for yourself. But it seems to me it's gotta be one or the other.

 

[chloec]

Hi @BobBridges Thanks again (!!) and your patience and kindness is appreciated.

I don't want or need my workbook to act like VBA, such as saving or deleting columns automagically. I just want my helper column to be incorporated into the formula as part of my "match" sequence (w index and match formula) so that there is no column to delete and maybe see if my formulas could be improved in general. My formula-based workbook DOES work, but it is not elegant, but if I can't improve the formula then I guess I will use it as is and take extra steps when prepping it to save as a CSV (the steps aren't that tedious).

I am so grateful to all of you for the thoughtful help and perspectives that you have provided. I'm happy to close this post so you don't keep getting ensnared! Haha. Ya'all ROCK !

 

[Marc L]

I was hoping someone would know how to incorporate my "helper A" column into the formula,

Even if you can get rid of Import worksheets column A you will have to operate manually after like I described in my previous post.​

But if you regulary need to reformat data in order to export them to text files, even if the frequency is once a month,​

the better way is to automize it via a VBA procedure which just needs the source raw data worksheet without Import worksheets.​

For this I just need to know how must be the data structure of each text file as you can easily describe each field name (header)​

and its relative source raw data column (header) or if it should contain a hardcoded value, a concatenation, whatever …​

That just needs VBA basics, nothing advanced.​

​