Find value in concatenated string and replace whole block of value instead of the partial

[therealdees]

Good afternoon,

I've been trying to create a dynamic concatenated cell, and for my use I found this user defined function to be useful. I have a dynamic column list that shows some category numbers depending on a true/false statement somewhere else. The formula is filled with the cell containing the concatenated values, followed by the array holding each value I want to find and then the dynamic array that either shows blank or the category number.

The problem is I need to replace the whole set of characters, but since I'm looking through a partial match the formula turns blank only that part of the chain.


Does anyone know how could I edit this code below to reach my purpose?

I attached a sample holding the actual situation and the expected result. Any help is welcome.

Please Login or Register  to view this content.

[therealdees]

Anyone?

Sos

[cubangt]

If i understand correctly, maybe not, but if you are looking for multiple values within your D2 column, will it always be a set number of values? meaning are you always looking for 1, 2 or 3 values within your original data?

If you are, maybe you can first do a count for how many it finds within your value. if you are always going to be looking for say 2 values in your data, if it finds 2 then run your replace logic, if it finds one, then do what you need for that condition.

It may be overkill, but it seems that you need to first identify whether or not your conditions are valid in order to run and get the expected results.

[therealdees]

Hello cuban,

Thanks for replying

The sheet is just a sample, the total of substrings vary without any limitation. As the codes are built merging different other codes, each part is separated by a letter. What I'm trying to do is disable all the substrings that this especific part is found within.

Before I reached this point, I think I was using a similar logic to what you proposed. Instead a composed code, I had a single one with 6 characters that I LENGHT and SUBSTITUTE then divided the result for 6 (number of chars.). I was planning to adapt the same logic for counting purposes after I manage to make dynamic this new code array.

I just have no idea how to achieve this, considering it's a partial match (although something tells me I can use the same logic, as all codes still have the same lenght)

[therealdees]

I can get the starting character number of the match, I guess if it's a single match within the whole string. Maybe if I could get the number of each starting point I could point out that from there and + 31 characters (as the lenght is fixed) it should be replaced for "".

[cubangt]

yea basically when i work with dynamic data that also varies in size, i try to find parts of the data that is some what structured and consistent so i can work off that..

If i have time today, ill take another look at the file and logic and see if i can offer any more suggestions or help.

For replacing portions of the value, i would def look at using index/substring to only manipulate the portion you want.

[therealdees]

I've been trying around to come up with something, still no success. The problem is the number of nested functions, as there are about 250 items to look through and excel's limitation is on 64.

I'll be alert for any notification then. I hope you can help

[cubangt]

using the sample file you provided, would you be able to provide a little more specifics for how its expected to work?

what i mean by that is the following:

Original Data: 142M135P001C001T001D001E001S013321
Expected Data: 142M135P001C001T001D001E001S013321

Original Data: 011M052P001C001T024D001E001S010745; 011M058P001C001T012D001E001S010774
Expected Data: 011M058P001C001T012D001E001S010774

How are you using the DYN Code, Cat Code and Stat to get your expected results?

The function REPLACETEXTS wants the value to look up, the range to look in and the range to replace..
So in the above 2 samples, the first one had "True" in the stat column and the 2nd had "False"

Even though the 2nd sample does have the value you are looking for, why was it false? why werent both samples true?

Just trying to wrap my mind around the expected processing of the data.

[therealdees]

Hi cuban,

I think putting everything close together made the sheet a bit confusing. The Stat, Dyn and Cat are not related by rows to the Original Data and Formula Result, they just serve as the input range, where the DYN only shows the code if the statement is true. That way, the REPLACETEXTS becomes dynamic, whenever the code shown in Dyn turns blank, that part of the code turns blank too, as you can see in some cases there are "???P" missing after the "M" sequence. The idea is to turn the whole substring to blank, not just the matched part

[cubangt]

OK,i think im grasping what you are trying to do.. ill use this example:

Original
011M052P001C001T024D001E001S010745; 011M058P001C001T012D001E001S010774
Expected
011M058P001C001T012D001E001S010774

So based on the function, it found that "052P" existed in the first number but not the 2nd so instead of just replacing the text within the first number, you just dont need/want it
And since the 2nd number had the number, thats the one you want returned.
Is that all fair and correct?

If it is, i think you have the correct idea, but maybe just needs to be executed differently. By that i mean, if you have a set of codes that will always return a blank cell and a set of codes that will always return the number that you expect..
Then maybe doing a if exists would give you little easier execution? (Maybe)

You can determine if the code exists from your "Wanted" codes or "Not Wanted" codes and then run a substring routine to pull out the number you expect

Example for the above.
TRUE DELETE
135P 052P
058P 221P
242P 095P
001P 208P

Since 058P is in the original data and in your "True" column, then pull that number out and results would be shown.
If this works for your needs, then i think it would be a matter of using substring to pull out the number you need, and index to make sure you pull out the correct number if there is more than one..

I may have time to continue to help with this over the weekend, but it looks doable.

[therealdees]

Hi cuban,

I think you got it right, but to be clear, the change applies to the whole code chain, meaning that if "058P" is true, for every "058P" found, the return value would be the original code block (no changes): 011M058P001C001T012D001E001S010774. Otherwise, if it's FALSE, the whole block should be replaced for "" and result blank (only that block). It's tricky because there could be thousands of variations starting with "058P" or any other set and there could be dozens of codes separated by ";" inside a string, and I must only show those that are marked as true.

I thought about and tried using IF, by the logic that I have a code with 35 char lenght and the starting position of the "???P" is always the same, then maybe getting the position of each matched FALSE substring would allow me to replace for blank that matching point til the first ";" to the right. I got stuck when trying to deal with arrays in the formula, as I have from 001P to 254P...

I'm already thankful for the attention. I'll be waiting any notification

[cubangt]

So looking at the original data, they are always 35 in length correct? If that's the case, may be able to atleast get you closer to your goal.
it would be a combination of multiple formulas, so in the sample you have those 2 columns for your codes that you are looking and the 3rd (true/false) in your final file, are you wanting to keep that setup or would you prefer to have those lists on a separate sheet?

Im getting each formula setup and working, but then want to setup and test in a layout/structure of how you will have it so i can make sure everything is setup to work when you copy it into your setup.

[therealdees]

Hello cuban,

Sorry for the delay, I had to take care of some home stuff

Yes! They are always 35 lenght. As for the setup, the analysis will come from a different sheet, meaning only the formula will be separated, while the 3 colums (true/false, dummy code and dynamic code) will be sitting next to each other.

If you manage to reference to the 3 columns from a separated sheet, I think I can edit to the real worksheet I'm going to use. I'm afraid I can't share the whole file with you because it's filled with sensitive data and the file is over 300mb. Let me know what you can do. In case you need I can try to recreate a better sample, although it'd be difficult giving the dependency between the value strings.

[cubangt]

So i got something working and i believe is what you need or looking for, but had one last question, in your sample file, the last 3 rows 25, 26 and 27 show your expected results, but those codes are not in your list, is that just missing from your sample sheet? i believe it is, but wanted to make sure there was no other condition that you were needing, if its just a matter of missing from the sample file, then i already added it to my array and my results are as you expect.

With this function you no longer need to have the codes you dont want, just the codes you do want.("Codes") sheet

[therealdees]

Hi cuban,

The rows you mentioned or any other row from columns A, B and C, are not related to column D in row position. Column B and C serve as the input range for the REPLACETEXTS formula, but they don't necessarily have the same lenght as the array "ORIGINAL DATA" (column D) being targeted/filtered.

ORIGINAL DATA could have 50 rows, while column B and C only 5, considering that I could be wanting to filter only 5 prefixes (???P) throughout each of the 50 cells in ORIGINAL DATA.

I don't think I understood your last statement. You mean I don't need the input array anymore? Got me confused because the idea is to turn dynamic every set of code, meaning the ones I do want (true) will be shown, while the ones I don't (false) will be switched for blank.

Looking forward to see what you've built! Hopefully you'll save my job, or maybe get me promoted lol

[cubangt]

Not sure how to embed the screenshots into the message, but i have attached them showing the results and my last question about the 3 rows in your sample file.


Attachment 729931
Attachment 729932

So what i have done is create a "Codes" sheet where you can have the codes you want to find, simply list your codes in the "true" column and the function will loop thru each of them and determine if your data
contains any of them, if it does, it will add to a variable, once its done looping then it will populate the cell with all the values it has found.

The way i understood the "False" list of codes you had, i chose not to even check if its false, because it looked like if the data contains that code you dont want it anyway, so why look for those,
just look for the ones you do want and return nothing for the rest.

Take a look at the screen shots and let me know if that looks correct. if it does, ill clean up the logic and comment the areas where you can make a few tweaks to help fit your needs.

[therealdees]

I couldn't open the first 2 attachments, but the last pic seems to match the "original expected results", so it'd sure suit me!! I agree with the logic of not bothering with the false statements, as I'll hide them anyways, much smarter than how I was thinking.

Do you wanna try uploading the pics again?

[cubangt]

the 2 attachments are the 2 images that are shown in the post above, just screen shots for the sheet with your original data, your expected data and the results of the function i created.

If that all looks good, ill finish cleaning up the logic and document a little and upload the file for you to test.

[therealdees]

Looks good to me! Ill be waiting then

[cubangt]

Here is the file

[therealdees]

Cuban,

I can't thank you enough. I will adapt my file to the new "true" structure and I'll give you a feedback, but thank you very much for the clear explanation in each line. The last code is indeed interesting and might be useful to me in the future. I think the function will work perfectly given the results in the sheet you shared.

[therealdees]

Hey cuban,

The function worked as expected!! I thought it'd take me some time to run through +- 100k rows, but it was pretty fast.

Although there are 2 problems that came up: the function seems to keep running constantly and since the file is already heavy itself the workbook becomes much more laggy than it already is. Maybe there's a way to update the values only if there's a change within the "true" column?

The second problem is something I observed that might be an issue when the file is finished and being operated by the end user: whenever there are no codes at all in "true" column, I get a msgbox in vba with a "catastrophic error" and the same error for then on while the codes are added to the "true" column again



EDIT: Actually, the first problem turned out to be a conflict between something I forgot to update. The function seems to recalculate only whenever there are changes in the "true" column, which is great. A smaller related problem is that every time the workbook is opened, it has to calculate the value of each row again. Is there a way we can improve that?

[cubangt]

For the lagging during the over all processing, try adding the following at the beginning of the function and end

Add this inside the function, at the top just under the Dim results As String line

Please Login or Register  to view this content.

Add this inside the function, just under the Next c line

Please Login or Register  to view this content.

So there is a chance your "codes" list would be empty? If so then there needs to be a validation/check before trying to run the function to check if there are any codes to use.
If there isnt, then a popup message shows up instructing the user to populate the list of codes, if there is, then it continues to run and returns the values.

[therealdees]

It's actually probable that the column will be empty at some point, because they depend on selection of a ListBox, which list fill is dependent of another listbox. At some point, through the user navigation, the listbox might be blank until he selects something else in other listboxes. Tho I'm thinking about having a "run" button to perform the updates in the cells related to the analysis I'm trying to create. It's turning to demand too much processing to keep things updating in real time.

[cubangt]

ok, for past projects ive had to prevent real time processing because it was literally making the workbook useless. We split things up to allow "certain" small formulas to run instantly and the larger(macros) to be run on demand to help, that way the user was aware that once they press the button to run, that it will take a bit to allow it to process everything.

[cubangt]

i have a ton of meetings today, but as i can, ill see how to update the function to check before running for each code.

[therealdees]

This photo below shows where the function you created is structured at. Column L holds the concatenated codes that I need to filter, while column K holds the function with the result value. The file has over 100k rows and it's referring to 2019 only. I was planning to compile every year until today lol, so I guess I'll have to rethink somethings.

It works like this: with the results of your function and as the data is structured by date, I have a table with dynamic headers, that represents either the brand or the product category name. With this table, I then compress the date structure into weeks, instead of days, what makes the calculation lighter in terms of processing. The formula that populates the table reads the header code (brand or product, according to what's shown) and looks for it in column K with your function result. Because the "true" column is dynamic and dependent of a listbox selection, the table built to read your function result will only read what's being selected, and the final result is a chart with the performance of the selected products/brands in the respective period.

I thought maybe I could share what I'm trying to do in case you're interested or even have some suggestion

[therealdees]

ok, for past projects ive had to prevent real time processing because it was literally making the workbook useless. We split things up to allow "certain" small formulas to run instantly and the larger(macros) to be run on demand to help, that way the user was aware that once they press the button to run, that it will take a bit to allow it to process everything.

Exactly! That's what I'm planning to do next: determine the triggering moments of each calculation according to the necessity of their role.

And no worries, what you provided me is already of great use, thank you very much.

[therealdees]

Forgot to add the table print

[cubangt]

Here is a routine you can use to check if your code list is empty before running the function
This will return true or false and you can use that in a condition wrapped around the function to notify the user that the code list is empty and needs to be populated before they can proceed.

Please Login or Register  to view this content.

[cubangt]

I just ran the original logic on 1650 rows and took 1 sec to complete.. with the codes and without the codes, the validation i provided above gave me the message stating the list is empty.

Here is the updated FindandCopy function logic, this seems to work, but im sure can be cleaned up and made better, but for the purpose of testing for the codes, this seems to do the job.

Add this at the very top of the module: Dim notificationCount As Boolean

Please Login or Register  to view this content.

[therealdees]

Good morning, Cuban

Sorry for the delay, I had a busy day yesterday, but I saw your messages at night and tried the code above. I'm not sure if I did something wrong, but the code bugs at 'If IsRangeEmpty(Worksheets("Codes")....' line. Anyway, the first code you provided me, with the original setup (not editing according to the comments in the code) is of great use already. As this is a "prototype", a first version of what I'm trying to build, at this point I'm naturally focused in trying to find out if what I want is actually possible within excel, as I know that are probably better application for my purpose, but I ain't have the time yet to dedicade myself to learn.

I'm starting to think that maybe I'm walking into a dead end, because the more complete the file becomes, more laggy and bugged it turns to be. At this point I'm starting to worry which way should be the best to take, considering there'll be a final user to operate this "dynamic report", and therefore it should have a minimal satisfying performance.

The biggest problem right now, I belive is being caused by a conflict between the Listbox that populates the true/false statements according to its selected items and the function you've built that will read this true/false column to update automatically. Whenever I don't have the function in each of the +-100k rows, the column for the listbox updates properly, although it takes +- 10 secs each update, cause it has to find the respective name for the selected item to mark it as true, and since there are over 250 items to be found and the Listbox filling list is actually a dynamic offset formula (which means that theses items in the respective column will vary in volume and position order within the listbox) I can't actually make a static output range to these true/false statements, I need to find and match the respective names 1 by one, and that's been demanding a lot of processing/time.

When I populate the column with the new FindandCopy function, it takes about 10 sec or a bit more to calculate (which is fine) and the results are correctly the expected. The problem appears when clicking the Listbox to update with new analysis. When I selected other (single or multi) products through the Listbox, excel keeps calculating the result (it shows "Processing 12 thread 1-100% by the right bottom corner) forever and nothing happens. Whenever it hits 100% it just starts to calculate from 1%, over and over.

I thought this could be somehow a conflict between the Listbox code trying to input the new true/false values within the linked colum and the new function trying to update the codes according to this column that haven't been fully recaculated yet, so it stucks in this loop. Does it make sense?

I was also wondering. Maybe what you said about having multiple real time calculation will turn the workbook useless is probably my fatal end. To create the final chart that represents what the user is filtering, excel must count the number of those respective items in view, and for that I use a partial match countif in each cell containing your new function result. The problem is the dataset is a daily record for 2019 with 100k rows, and I've to check each day for each category, that makes 25kk cells with formulas being updated.

In case you're interested, let me know if it's understandable. Any suggestion is welcome.

Unfortunately I can't share the file, but I could recreate some situations according to your needs. Just let me know if any of this make any sense to you

[cubangt]

So i was reading and had a question about this statement:

The biggest problem right now, I belive is being caused by a conflict between the Listbox that populates the true/false statements according to its selected items and the function you've built that will read this true/false column to update automatically.

What is the purpose of the true and false in conjunction with the function i provided?

And then this statement:

I can't actually make a static output range to these true/false statements, I need to find and match the respective names 1 by one, and that's been demanding a lot of processing/time.

What are you using to do your lookup? VLOOKUP, HLOOKUP OR XLOOKUP?

[therealdees]

In the first image, the user can filter between different listboxes to reach the filtering result in the last listbox to the bottom left, which holds the products that corresponds to that filtering condition. This last listbox is the only listbox that has a direct impact on the chart, meaning that through a code, whenever there's a change in it, it'll find and match in the true/false column (shown in the 2nd image) the items that are populating the list. If they are selected, it'll input true, or vice versa.

The 2nd image shows the sheet that I improvised the dependency among the listboxes using offset and if formulas. The listboxes are working fine, although I plan to optimize them later.

The 3rd image shows the original concatenated codes, and the column that will hold the FindandCopy function, that depending on the true/false results, will show only the codes that are being filtered in the user analysis.

The 4th image shows a table structure built to count the number of items shown in the FindandCopy column, according to the code placed in the column header, that dynamically based on a dashboard selection button, shows either the brand or the product name/code.

The 5th and last imagem shows the same structure, expect it compiles the data in weeks, instead of days, making the table smaller (considering I plan to add at least 1 more year for comparison)


Does it make sense to you? Do you think any part of it is built way too improvised and could be thought differently?

[therealdees]

So i was reading and had a question about this statement:

The biggest problem right now, I belive is being caused by a conflict between the Listbox that populates the true/false statements according to its selected items and the function you've built that will read this true/false column to update automatically.

What is the purpose of the true and false in conjunction with the function i provided?

And then this statement:

I can't actually make a static output range to these true/false statements, I need to find and match the respective names 1 by one, and that's been demanding a lot of processing/time.

What are you using to do your lookup? VLOOKUP, HLOOKUP OR XLOOKUP?

The purpose is to turn the counting of the items dynamic, according to the user selection in the listboxes

For the find and match, I'm using the following code:

Please Login or Register  to view this content.

EDIT: The code also carries a hide/unhide function, that's supposed to filter the brand or product that will be showing in the chart

[cubangt]

Id have to think about it a bit, but just thinking out loud here, on the main page where they are making the selections from the list boxes.. does each list box update or change the next box real-time? or are they all independent and only update once i make a selection in the last box?

I don't know or think this would work for what you are doing, but I've worked on similar reports for other departments and the way we were able to handle such large amounts of data filtering and keeping down the amount of time was to use slicers instead of list boxes, we had all our data within each tab in its own "table" not pivot table, but just a excel table.
Using the slicers was a huge improvement for our report.. something to consider or try if you have the time.. (granted our report may not have been as involved as yours, but it allowed for a nice dashboard screen)

Today and Tomorrow my calendar is booked up, so may not be able to provide much help until Friday..

Also something to think about is adding the "updating/alerting" = false before running your different routines and then turning it back on afterwards

Application.ScreenUpdating = False
Application.DisplayAlerts = False


Application.ScreenUpdating = True
Application.DisplayAlerts = True

Please Login or Register  to view this content.

[therealdees]

Thank you for the tips, I'll sure try.

I've thought about slicers before and actually tried them in some other reports, but my perfectionism disease made me give up as I found them to be ugly in that case. It's a great tip though, I will try them right now and see how it goes.

Today and Tomorrow my calendar is booked up, so may not be able to provide much help until Friday..

Ill be waiting any notification then

[therealdees]

Id have to think about it a bit, but just thinking out loud here, on the main page where they are making the selections from the list boxes.. does each list box update or change the next box real-time? or are they all independent and only update once i make a selection in the last box?

Forgot this. They are indeed dependent. Whenever I click a item in the first listbox to the top left, the listbox below is updated with the following dependent items of the respective ones selected above. It follows that hierarchy until the last of it. The only exception is the only box to the right, at the top. This one holds the brand names, that are shown based on the Listbox to the left (the first in the hierarchy). Right next to it there's a selection button that will determine the headers of the table which the chart is built on. That means the user can toggle between Brand view or Product view, but the logic for the filtering through the FindandCopy is the same, except I was planning to adapt the function to switch between ???P and ???M (which represents the brand part of the code)

[therealdees]

Maybe I could use a button to trigger the update within the FindandCopy? I think it's a problem of multiple parallel calculation that depend of each other and causes the catastrophe. Maybe the solution would be having something semi automatic instead of dynamic. The user toggles between the filters, and the chart will respond hiding/unhiding the respective columns, but the values will still be from the last analysis, or even blank, so whenever the user clicks on this button, then the column with FindandCopy will recalculate and populate the chart with the correct values.

What do you think?

[therealdees]

Hey cuban,

I've kept working on some other functions the dashboard will provide, and I belive to have almost finished the reports that must be included. The project is beginning to gain form and I'm starting to care for design now, what makes me glad.

I belive my mission now is to optimize the responsing of the tool, as the file is extremely laggy and at this point is only usable when dealing with manual calculation by pressing F9. Anyway, I made some progress by diminishing it to half size when I realized I could use the =SUMPRODUCT to sum the arrays of the SKU counting, instead of using a massive dummy table with millions of =LEN() formulas. The problem is that wasn't enough, although the file size went from 250mb to 140mb it still very laggy and I belive it's because of conflicting formulas, in a sense they compete for calculation as they depend of each others result. I'm no expert - in fact I'm a true noob - so take that as a hunch.

One thing I still have in mind is what you said about having issues with real time calculation. I'm convinced that if I'm able to hierarchically define their triggering moment according to their roles, I shall then get the best results. I have no idea how to do that, but I'd maybe start trying to convert the user function you provided to a "normal" vba code and assign it to a button "RECALCULATE". That way I could use automatic calculation as somethings depend on it to function, e.g the listboxes and the other charts. The only thing that wouldn't be updated in real time I guess is the main line chart that's directly linked to the last listbox (product category selection), and by the time he presses the button the chart will then update according to the user filtering scope.

I think the conflict problem I mentioned comes from the fact that whenever the listbox is clicked excel runs a code to loop the selected items, find them in a dummy column and mark the adjacent column with true/false. I belive that simultaneously the FindandCopy function is trying to read that those changes in real time while the other code still looping thru +256 rows to input the true/false statements and it starts to work in a redundent way. If the function is recalculated only after the user finishes filtering, I belive the problem is gone.

I was hoping you could help me out at this last step of my journey. You've been crucial so far with the last code you provided, without I would've been stuck by now, but the sooner I finish this the faster my life will become better as I might get promoted for fixing a big problem.

This is how the pannel is looking right now:

[cubangt]

if i remember correctly, the function only needed the range correct?

If so, all you would need to do is add your button, then on the click event of that button, just place the following:

Call function-name-here(Range)

On the front end, since you can merely select a range by choosing your cells, you would have to do the same within your button event to pass into the call.

As for seq of events to run them one after the other, i believe an IF/ELSE would work.. but not 100% sure.
If there is a chance that the routines are fighting for or running in parallel maybe that would help.


Here is an example that may help understand what to do: found it on this site: if run then run

Please Login or Register  to view this content.

[therealdees]

Hey cuban,

I actually changed everything and now the file works better. It went from 260mb to 65mb so far. I realized I didn't need the find code to input true/false for the selected items in the listbox. I could just retrieve every selected value in a column and create a true/false with a countif function. That saved me a lot of processing as the listboxes were all nested.

The problem turns to be the Findandcopy formula. For some reason, sometimes it updates blank when I'm clicking through the category listbox. Although it takes some time to update, it returns nothing, but sometimes it works as expected. I have no clue why.

Once again I turn to think maybe a VBA function would be more efficient instead of the function that will keep recalculating. Maybe we could build something that does the same but built to work with a button? Whenever the button is clicked the 2 columns holding the findandcopy function (yes, there're two now because I need to count the returned products) are updated using the same logic and structure we built. I believe we could use the function as you proposed, calling it out with a button and maybe limiting its recalculation to that moment, but for some reason I believe the user function is somehow a more complex task for excel to execute, and my goal now is to turn the file the most efficient I can.

I'm sorry if it seems like I wasted your time, but I belive it was a crucial starter, especially for the logic needed to perform what I need.

[therealdees]

Btw, I saw your PM reply, but since I changed everything maybe things are easier now so I decided to post here.

[cubangt]

You can call it just once instead of within every cell, just add your button, call the function on button click and pass it the range, in this case the column you want it applied to.. BUT it may need to be tweaked, but you can try first before making changes.

[therealdees]

Hey cuban,

I'm trying to figure it out how to do what you said. I created the button and added the following:

Please Login or Register  to view this content.

But it actually makes no sense if you said I don't need to have the formula in every cell anymore. So how is it supposed to know from where to extract the SKUs? Can you edit to show how it's supposed to be?

Please Login or Register  to view this content.

PS: With this method, will the Findandcopy function update itself whenever I make a change within the workbook/sheet as it's been doing? If so, the problem would persist, it'd be like pressing F9 after each filtering that is done (I found out the findandcopy returns blank probably because of the conflict, but if I make no changes to the listbox and press F9 then the formula would return the expected values and the chart will then populate)

I need somehow to make this formula or sub code run only when a button is clicked, that way the listboxes would have time to finish calculation to return the codes for the selected items, so the user could then click the button to update the chart. That way there's no conflict and even the listboxes I assume would run faster as there's no other code running simultaneously

[cubangt]

had to find the file i had created to look at the code, since you want to call it from a button, you will need to loop thru your column of skus to provide the value to the function and populate the cell with the results..
Let me see what i can do between my meetings.. ill post back what i find.

Since you will be moving this to a button for execution, you will no longer need to have the formula function in a cell on your sheet, it will be called in the code and the cell you designate will end up with the results..

[therealdees]

Thanks!!


edit @ ur edit: exactly!! if you could do that it'd be great. no needs for 100k cells with formulas, I'm guessing it'll perfectly solve the problem

[cubangt]

Ok, here is a routine you can call from your button, right now this will run on the active sheet, but you will need to add logic to make sure it runs against the correct sheet in your workbook.

Also may not need the If Not IsEmpty check, so you can try with and without it and see..
Because this is going to loop thru your list of sku's it will still take some time to process, but at least it will only run once all your other processes are complete, so that its not fighting for resources.

So the only things you will need to change is the column letters of where you want to place the results and the column that has your sku's and add reference to your worksheet.
remember the function routine does all the work, all you are doing here is to make the master call to run it against all skus one after the other instead of everytime something changes on the sheet.

A = Sku Column
D = Result Column

Please Login or Register  to view this content.

[therealdees]

I don't get it. So I should use this to call the Findandcopy user function? Or should I just place this in the button call?

[therealdees]

How does this relate to the function and prevents it from autoupdating?

[cubangt]

you can take the logic within the Sub and place that in your button event., it will loop each row of skus and for every sku, it will call the findandcopy routine and populate the cell you want with the results of that routine.
Since you will remove the function call from your cells, this will only run when you click the button, it will not autoupdate anything, you will need to click the button everytime you want this applied.

Does that make sense? if you want, you can change the Findandcopy from a function to a Sub

[therealdees]

Yes! It does. I'm studying it and got to understand the logic, but it didn't work, not sure if I did something wrong, but I edited as the following:

Please Login or Register  to view this content.

There's one thing tho, I was perfoming the task twice, one for sale tickets and other for returns/refunds, not sure if I should make the code run twice one after the other or if it's best to make it calculate within a single code.

I have 2 columns with the SKU data, the sale is in column "R" and the returns/refunds is in column "S". Column "N" will hold the results for the sale SKUs, while column "P" the return/refunds results. It doesn't pop any error, but nothing happens also.

Both columns for the results are blank, no more formulas inside, just waiting to be populated. Any idea what I did wrong?

[therealdees]

PS: FindandCopy function is placed as a module called "Dynamic_SKU", not sure if it's relevant for you to know


EDIT: PS2: What do you think will work more efficiently? The user function or converting it to a regular sub?

[cubangt]

PS: FindandCopy function is placed as a module called "Dynamic_SKU", not sure if it's relevant for you to know


EDIT: PS2: What do you think will work more efficiently? The user function or converting it to a regular sub?

Im at lunch, can you send me a file with just the skus in the columns you currently use

[therealdees]

https://we.tl/t-bh1rEOgUNd

The file is too big to attach so I added it to wetransfer

There are 5 columns:

"T" = original concatenated SKUs strings

"S" = return/refunds filtering (from column T)

"R" = sales filtering (from column T)

"P" = result for Findandcopy over column "S"

"N" = result for Findandcopy over column "R"

[cubangt]

So will you still be using these codes?

135P
058P
242P
001P
035P
030P

Also, is that 3 sets of skus im seeing? (R, S and T)

I just ran the logic against a few of the skus and it updated column N with the results..

I used the sku column T and Updated column N with the results..

Please Login or Register  to view this content.

[cubangt]

Found why its not doing anything, the count isnt working.. ill post update in a min. for my sample file i was using my row 1 had the header and the data started on row 2, because its getting a count, need to address that.

[cubangt]

Ok, i added a button to my file, and made the code run from the button click

took about 6 seconds to populate 9400+ rows
Since i wrapped it within a WITH, had to update all the Cells( to .Cells( notice the period in front of each..

below is setup to use column T as the sku list, then update and populate column N with the results.

Please Login or Register  to view this content.

[therealdees]

Great!!! Will test it right now.


As for the code list you asked about, these ones you posted are just a part of a total. There are about 256 codes that will be populating the column according to the user selection.

[therealdees]

Oh, about the SKU columns, there are 3 because 1 holds the original data, while the other two represents either the sales or the return tickets

[cubangt]

Oh, about the SKU columns, there are 3 because 1 holds the original data, while the other two represents either the sales or the return tickets

So column S is the sku list you would use to run the findandcopy against that would place results in your column P
So column R is the sku list you would use to run the findandcopy against that would place results in your column N

Column T is not really used since you have that list split into sales and returns columns correct?

And both sales and returns will use the same "code" list?

[therealdees]

Exactly! So I wonder if I should run the code twice or should it run together in one click


One other thing I was planning to do and forgot to mention: the chart will respond to a selection button (1 or 2) that determines whether to show the x axis with the brand names or the category names. The brand codes are also 4 lenght, the only difference is that instead of a "P" it follows a "M". My idea was to add the codes with the same logic as the category (thru the listboxes) to the same column, and when find and copy runs, it'll hide either the category or the brand name. That way, I can filter both brand and category in both views (brand view or category view).

I think it's a bit confusing, but basically the selection button will change the headers in the chart, and these headers are followed by a true/false that is used to hide the columns (thus the labels in x axis) whenever there's a change within the listbox. The point is, at the same time clicking the listboxes will populate the code list to search with findandcopy, it will also hide the columns of the table chart if they are not selected in the listbox.

Does that make sense?

[therealdees]

Anyways, I'm just saying. I think it'll already work for that too. I'm trying it now, will tell you in a moment

[therealdees]

First attempt seemed to work at first, but then completely freezed and I had to restart. I'll give it another try and investigate

[cubangt]

Exactly! So I wonder if I should run the code twice or should it run together in one click


One other thing I was planning to do and forgot to mention: the chart will respond to a selection button (1 or 2) that determines whether to show the x axis with the brand names or the category names. The brand codes are also 4 lenght, the only difference is that instead of a "P" it follows a "M". My idea was to add the codes with the same logic as the category (thru the listboxes) to the same column, and when find and copy runs, it'll hide either the category or the brand name. That way, I can filter both brand and category in both views (brand view or category view).

I think it's a bit confusing, but basically the selection button will change the headers in the chart, and these headers are followed by a true/false that is used to hide the columns (thus the labels in x axis) whenever there's a change within the listbox. The point is, at the same time clicking the listboxes will populate the code list to search with findandcopy, it will also hide the columns of the table chart if they are not selected in the listbox.

Does that make sense?

You could call it per column, if you are wanting to only update based on those individual sku lists.. you could run it against the sales sku list, once complete, run it against the returns sku list.

And for the codes, if you are wanting to use another list of codes for another purpose, you can add them to the code sheet, and just reference that sheet with the appropriate column that contains the list of codes you want to use..
Anything that is currently hardcoded with absolute cells or column letters, can be updated to be more dynamic.

[cubangt]

First attempt seemed to work at first, but then completely freezed and I had to restart. I'll give it another try and investigate

If it freezes again, let it continue and see if it errors out or if its just that much of a processing hog..

[therealdees]

It's funny because it starts with a mad clicking like if it was running, and a bit flickering in the screen, but suddenly the mouse pointer becomes the loading sign and the window gets labeled as "not responding". It's been running for +- 4 min and still freezed. Maybe it has something to do with blank cells between the SKUs throughout the column?

Or do you think for being a user function it's demanding mad processing?

[therealdees]

I find it weird, because usually when the software freezes for me while waiting to finish a code, it just stuck, you just can't click anywhere etc, if you insist then I get the not responding label, otherwise it just waits with the loading sign. In this situation it's weird because it goes straight to the not responding. Just a observation.

[cubangt]

yea i tried some changes and this time it really locked me up and not responding either.. im pulling some of the code out to see what happens.. if it doesnt improve, may have to see if anyone else can chime in and help.. im trying to exhaust anything i can before giving up..

[therealdees]

yea i tried some changes and this time it really locked me up and not responding either.. im pulling some of the code out to see what happens.. if it doesnt improve, may have to see if anyone else can chime in and help.. im trying to exhaust anything i can before giving up..

don't give up pls, we're so close

I assigned the code that hides the chart columns to the "update" button. The listboxes work better now. I was trying to go with the findandcopy once again, and maybe we could turn that function into a vba code that does the same job. I belive the processing is mad because is a function, I don't know.

What do you think?

[therealdees]

My idea is to assign to the button the code that activates the SKU task, right after it's finished it'll run the hiding columns code (within the same code sheet, as a subsequent code)

[cubangt]

OMG!!!! (this is not aimed at you, just at myself for assuming that those lines would help)

You wont believe the solution.. i went in and remove any reference i had to these 2 lines everywhere and it literally updated all 9400+ rows in under 5 seconds with no locking..

Please remove any of these within the function

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

[therealdees]

As for the hiding code everything works fine. It's just a matter of filtering the SKUs efficiently and all the rest is tied up and working as expected. I think a user defined function might be the problem 'cause it takes about 10-15 min to calculate when there's a change or update. If I could make somehow this task in 3-5 min I'll be glad enough and the file can be usable

[therealdees]

OMG!!!! (this is not aimed at you, just at myself for assuming that those lines would help)

You wont believe the solution.. i went in and remove any reference i had to these 2 lines everywhere and it literally updated all 9400+ rows in under 5 seconds with no locking..

Please remove any of these within the function

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

let me try

[cubangt]

man, i just ran it a few more times to make sure it wasnt just a fluke, and each run took under 5 secs with barely any cursor blinking.. NOW keep in mind, my dataset im using is only 9400+ of your skus
My sample file does not have all your code and formulas.. so my results will not be the same as yours.. BUT the function processing is def quicker now without those lines..

[therealdees]

Yes, that's awesome already!! It seems stable now, been running for about 4 min, lets see

[cubangt]

i would have tested on your 17mb file, but its just the file and didnt have your macros, so figured id use my file and just copy the skus over to my file to run..
Hope that helps and completes successfully.

[therealdees]

It still running. I was wondering and this might sound stupid, but is there a difference between the efficiency of a user defined function and a "regular" vba code? As far as my knowledge goes, I see they are two different things, or do they behave the same way?

Would it be more efficient to reproduce the same behavior using direct referencing for the ranges etc? Does it demand more processing if you're running a code to call out another function?

[therealdees]

If it's true, and if it's not something that would demand and bother you much, we could just turn that into a normal code looping through each cell etc and maybe things could work out faster.

[therealdees]

I will clean the most sensitive data I can so I'm able to share the file with you. Just let me wait some more to see if the code goes through the task

[cubangt]

Not sure honestly..

A sub performs a task but does not return a value. A function returns a value of the tasks performed.

I dont think it would make a difference, but you can always try and change it from a function to a sub.. and see..
Everything i've read shows very small differences in performance between the two..
only reason it was setup as a function was to expose it to be used within the cell.

with that said, you can still get a value from a sub by declaring a global variable and assigning the value to that from the sub..

[therealdees]

Yeah, I know. At that point that was the only way I could think of. I actually started trying formulas, so this is great advance already.

The code still running now, I will end the application and clean the file so I can send it to you.

You can try to tweak through the listboxes to see how it behaves to determine if the problem could be elsewhere than the function itself.

[therealdees]

Hey, it's working tho, as I click END to stop it from running, it shows me some partial results that are updated within the chart!! This is good

[cubangt]

how many rows does your sheet have with all the sku's, not that it matters, just want to see what could be done differently

[therealdees]

There are 106k and something rows. The problem is that there are 256 different "???P" to loop through, and if I'm able I was thinking about adding the "???M" that would be about 150

[cubangt]

Hey, it's working tho, as I click END to stop it from running, it shows me some partial results that are updated within the chart!! This is good

Def a good sign.. just need to look at everything else to see if there is room for improvements..
If you are using any VLOOKUP or HLOOKUP, those will def slow things down and would be better as index/match

but i can take a look once you provide the file.

[cubangt]

There are 106k and something rows. The problem is that there are 256 different "???P" to loop through, and if I'm able I was thinking about adding the "???M" that would be about 150

ok, then ill focus on that routine to see if it can be improved. make sure the file has all those codes so i can test with..

[therealdees]

In the way I'm building it, the user could at somepoint select all categories to see the full $$ income of those items, or any other info for all of the products sold together. In other hand, he could be interested in looking into just a couple of them, so compare performance and have a more detailed view. All of that depends on the listbox filtering, that will input the "???P" codes to filter

[cubangt]

ok, not a problem, just want to look at all aspects of the overall processing of the selections..

[therealdees]

I sent you the link thru PM

[therealdees]

In case you need guidance to understand it I'm ready for any reply

[cubangt]

ok, ill take a look and post back as soon as i can.

[cubangt]

trying to work on this at home and my pc cant handle the file. May have to wait until morning..
I did a search of all the formulas on the sheet "tickets" and it found you are using vlookup in 697878 cells
example in H4 :
=IF(G4="","",IF(OR(G4="C/F",ISERROR(VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE))=TRUE),"N/D",IF(VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE)="","N/D",VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE))))

that many vlookups may be the entire problem.. a good read on vlookup can be read here: vlookup

If at all possible i would consider replacing the vlookup with index/match, should be more efficient and a lot faster with the amount of data you have.
Ill have to work on this tomorrow at work when i have a better pc to use.

[cubangt]

Since not all columns had data to play around with, i made up some and changed your formula in column E "Nome"

From:
=IF(N4<>"",IFERROR(VLOOKUP(G4,CRM!$B$2:$R$105365,3,FALSE),"C/F"),"")

To:
=IF(N4<>"",IFERROR(INDEX(CRM!$B$2:$R$105365,MATCH(G4,CRM!$B$2:$R$105365,0),3),"C/F"),"")


Since you have so many formulas, i wanted to just take the smallest of your vlookups and change it up to index/match

maybe you can try one column at a time and test for improvements..

Something else to consider is making your range references dynamic so that if you add or remove data the formulas that have them, will work no matter how much or how little you have.

good read for that can be found here;
dynamic ranges

[therealdees]

trying to work on this at home and my pc cant handle the file. May have to wait until morning..
I did a search of all the formulas on the sheet "tickets" and it found you are using vlookup in 697878 cells
example in H4 :
=IF(G4="","",IF(OR(G4="C/F",ISERROR(VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE))=TRUE),"N/D",IF(VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE)="","N/D",VLOOKUP(G4,CRM!$B$2:$R$105365,11,FALSE))))

that many vlookups may be the entire problem.. a good read on vlookup can be read here: vlookup

If at all possible i would consider replacing the vlookup with index/match, should be more efficient and a lot faster with the amount of data you have.
Ill have to work on this tomorrow at work when i have a better pc to use.

Hey cuban! I'm sorry for the late reply, had a full day today I

This is actually great, makes me happy to have at least a clue of what to do. It is completely possible to change everything to index/match if needed. Let's see what else you reckon and I'll put my hands to work

Thanks for sharing the link. I did heard about vlookup using lots of processing, but didn't consider it could be the problem. I will sure take a look at the article

[therealdees]

Since not all columns had data to play around with, i made up some and changed your formula in column E "Nome"

From:
=IF(N4<>"",IFERROR(VLOOKUP(G4,CRM!$B$2:$R$105365,3,FALSE),"C/F"),"")

To:
=IF(N4<>"",IFERROR(INDEX(CRM!$B$2:$R$105365,MATCH(G4,CRM!$B$2:$R$105365,0),3),"C/F"),"")


Since you have so many formulas, i wanted to just take the smallest of your vlookups and change it up to index/match

maybe you can try one column at a time and test for improvements..

Something else to consider is making your range references dynamic so that if you add or remove data the formulas that have them, will work no matter how much or how little you have.

good read for that can be found here;
dynamic ranges

I was planning to turn everything dynamic after I finish, so I could update the file easily, with minimum manual interference, thus errors. You think using named ranges and dynamic references will also improve its performance?

About the Listboxes, did you understand the logic which I created the dependency of them? Do you think that could be a processing problem also? Is there anything else you noticed to be inefficient?

[therealdees]

Hey,

It fuckin worked!!!!!! I changed the formulas to INDEX and I have no doubt it should've helped the processing, but the problem persisted anyways, so I thought maybe I should somehow go back to the idea of separating the tasks and then I remember this thing about turning manual calculation on/off and it fuckin worked. Since I set the formula to run only with the button, I added

Please Login or Register  to view this content.

at the top and then turned it off at the end and it took about 5 disappointing minutes, but it did work.

If you're still interested, I'd be happy if you could take a look and see if there's anything else we could improve to turn this 5 min to the least, but I thank you very very much for your help so far. I started with excel in january, all I knew was the =SUM function, and ever since I've been self learning thru the internet and thanks to people like you I managed to get this far and complete some important tasks that might get me a better job soon.

[cubangt]

I took a look at the listbox codes and had a few questions..
I haven't worked with them in excel, so cant offer to much help on handling them in excel.

But wanted to ask about your setup.
I noticed that the listboxes seems to be built from the data in the engine 1 tab, and as you make a selection on the dashboard, the value is captured on that sheet. Is that correct?
If so why do you need to keep that? Since you know the selection, is there a real need to keep that data?


Have you looked into cascading listboxes?
https://support.microsoft.com/en-us/...3-acabacb76605

Setting them up as cascading listboxes, doesn't involve vba code and MAY improve performance..

Also, didn't see much code with ranges, but a few of the larger range sets, may considering reading the range into an array, would be quicker. http://www.cpearson.com/excel/ArraysAndRanges.aspx

One last question, why did you place all your routines into separate modules?(just curious)

Here is a good read on writing VBA that may help with any future updates or creations.
https://corporatefinanceinstitute.co...-vba-in-excel/

[therealdees]

Hey cuban,

I'm not sure if I understand correctly, but I need the data in Engine I (at least as far as my knowledge goes) to produce the dynamic offset arrays that will fill each subsequent listbox. Does that make sense?

For instance, BoxA has 5 items, and each of them will hold other items that might be unique (or not) for that specific selected item. It's a cascade effect that is build on the true/false output that comes from the selection of the items.

How could I maintain the same behavior without having the data in the sheet? Is it possible?

I actually saw something about the cascading listboxes, will check it again, but if I remember I actually tried it and it didn't work for some reason. Will sure investigate

@ placing the routines in separate modules there's no specific reason besides me being noob. Does that compromise anything? How'd you have done? In a single module?

[therealdees]

The big deal with the listboxes is this offset formula that works together with a IF function in each item of each listboxes. So for instance, if there are t-shirts being sold as a Sport item and also a Apparel item, there will be 2 dependency, so right next to the unique filter (that lists every subsequent item filtering from the product code table) there is a IF function for each of the items and their respective depedency. That way, the array is compiled into a no-blanks array containing only the items related to the ones selected.

It's a massive confusion, I did this without much know how, and it's the only way I could think of

[cubangt]

Hey cuban,

I'm not sure if I understand correctly, but I need the data in Engine I (at least as far as my knowledge goes) to produce the dynamic offset arrays that will fill each subsequent listbox. Does that make sense?

For instance, BoxA has 5 items, and each of them will hold other items that might be unique (or not) for that specific selected item. It's a cascade effect that is build on the true/false output that comes from the selection of the items.

How could I maintain the same behavior without having the data in the sheet? Is it possible?

I actually saw something about the cascading listboxes, will check it again, but if I remember I actually tried it and it didn't work for some reason. Will sure investigate

@ placing the routines in separate modules there's no specific reason besides me being noob. Does that compromise anything? How'd you have done? In a single module?

I was just curious, ive done it both ways, but had it in a way that each module served a certain type of purpose.. i haven't heard that its better one way or another, more personal preference..
I understand what or why its setup that way, just wondered if cascading listboxes was not possible in your case, i personally have only worked with user controls once maybe twice about 2yrs ago and only because i was helping maintain something that was done by a previous employee..

but when designing applications and website that do the same thing that you are doing, we wouldnt populate until selections were made, but used the "selection value" as the parameter for the next box.. so maybe not possible in excel..

[cubangt]

The big deal with the listboxes is this offset formula that works together with a IF function in each item of each listboxes. So for instance, if there are t-shirts being sold as a Sport item and also a Apparel item, there will be 2 dependency, so right next to the unique filter (that lists every subsequent item filtering from the product code table) there is a IF function for each of the items and their respective depedency. That way, the array is compiled into a no-blanks array containing only the items related to the ones selected.

It's a massive confusion, I did this without much know how, and it's the only way I could think of

Hey whatever works and gets the job done is a great way to start. I am no expert, so cant say its wrong, just throwing out ideas and suggestions..

[therealdees]

I was just curious, ive done it both ways, but had it in a way that each module served a certain type of purpose.. i haven't heard that its better one way or another, more personal preference..
I understand what or why its setup that way, just wondered if cascading listboxes was not possible in your case, i personally have only worked with user controls once maybe twice about 2yrs ago and only because i was helping maintain something that was done by a previous employee..

but when designing applications and website that do the same thing that you are doing, we wouldnt populate until selections were made, but used the "selection value" as the parameter for the next box.. so maybe not possible in excel..

I see. I will def checkout the cascading boxes, maybe it could get me more efficient

Hey whatever works and gets the job done is a great way to start. I am no expert, so cant say its wrong, just throwing out ideas and suggestions..

Well, you're expert enough to have helped me a lot. Thanks for that! I will keep working on it to see what I can improve, but at this point the file is useful already

Will set the post to SOLVED and if you're interesed I'll give you a feedback of how it's looking when finished.

s222

[cubangt]

I see. I will def checkout the cascading boxes, maybe it could get me more efficient



Well, you're expert enough to have helped me a lot. Thanks for that! I will keep working on it to see what I can improve, but at this point the file is useful already

Will set the post to SOLVED and if you're interesed I'll give you a feedback of how it's looking when finished.

s222

Yea if you make more improvements on overall processing and get it where you need it, def let me know.. glad i was able to help a little to get you further.
p.s. the questions i asked earlier were not questioning your logic, just for me to better understand the overall process of how you have it working..

[therealdees]

Hey cuban,

There a conflict when trying to add the brand codes to the true/false column that serve as reference for the Findandcopy function. I mentioned before that I was planning to filter both brand and category as the user desire, so I added the codes to the same column so the findandcopy would filter any of them. It's kind stupid I didn't realize it ealier, but now that there are 2 matches, we're dealing with 8 characters, and the formula won't respond correctly when there's a double match (both brand and category in the same substring) because it's trying to subtract 4 characters within the scripts logic.

Any ideas to work around this?

[cubangt]

Without seeing your sample record, i can only assume that you are stating that within 1 string, there are now 2 codes you want to find and because the 2nd code is not in the same place as the first one, its creating some wrong return values..
if thats the case, then a few things have to happen.

One you will have to separate the codes so that you can run the lookup against each list separately. And based on where the codes are found, then adjust the "indexing" and the total character count based off that..
In other words, if code 1 is found then it uses what we already have written, if code 2 is found then the index and total is determined based on that.

If you can provide 1 sample value with a mix of the codes i can see what i can do..

[therealdees]

Good morning cuban,

I think you got it. Whenever a string has an occurrence of the 2 partial codes, it bugs. I was working on it yesterday night, and I thought exactly what you said (kinda stupid I didn't think before). I can make the run with the brand codes "???M"? and that's all I need, since the table is reading the product codes with the sumproduct, it will read what's been filtered out already from the brands.

I had to change everything. I deleted the listboxes as they were giving me too much trouble. I'm trying to do something not meant to be done in excel, I might need a database and managing softwares, etc, so I accepted my reality and I'm trying to make it as simple as possible.

I made another post asking for help to transform your user function into a sub function (I'm not sure if a standard sub would be written in the same way, but maybe it's worth trying to see if it improves. After editing everything, I managed to recalculate the sheet in 3 minutes when pressing the button, which is great already, but something tells me I can get like 1 min, 'cause the file is pretty lighter now.

I hope you don't mind I posted your coded asking for further help, but if you're interesed I'd be pleased with your assist

[cubangt]

i subscribed to the other post

[therealdees]

It didn't work, or at least within 7 minutes I've waited. I think I'll use the user function, I can deal with the 3 min, it's great already


I'm investigating a problem with having to click 2 times the recalculate button for the chart to update (although the other sheets have the data updated already the chart sticks in the previous selection), when I'm done I'll let you know how it's going

[therealdees]

Hey, any idea why the results are coming like this:

Please Login or Register  to view this content.

Looks like it's been sliced at the wrong place. Maybe the function needs changing as new set of code "???M" starts in the first character of the SKU, not in the forth anymore. Besides I get a #VALUE error in some cells.

This is the user function I'm using:

Please Login or Register  to view this content.

[therealdees]

PS: I solved the clicking issue with a couple of band-aids: in both listboxes that are left I added Application.Calculation = xlCalculationManual and for the update button I assigned a Application.CalculateFullRebuild at the top followed by the hiding columns script and a Application.Calculation = xlCalculationAutomatic at the bottom. I'm not sure exactly why it worked, but it calculated in 1:26 sec perfectly (before I changed to read the brand codes)

[therealdees]

I changed and it worked

Sorry


results = Mid(origData, (InStr(origData, c.Value) - 0), 34) & "; " & results

[therealdees]

By the way it worked in less than a minute!!!! fuk im so happy

I think it's because the partial code it's looking for it's at the very beginning of the substring, so it's easier to find. this is ******* awesome i love you

[cubangt]

yea if you are going to run the results using different code and if they are located in different sections within the string, then you will need to make sure that the starting position is adjusted accordingly to the code you are looking up..
Example would be if codelistA are always found in position 4, then use the one i originally created.. and if codelistB starts at the beginning then use your updated version above. and so on..
As long as you know where things will be located within the value, you can adjust the starting position so that it returns what you expect..