I have a spreadsheet that am needing the data on the first tab to automatically display information that reads from a second tab list.
What I'm running into is that the first page data needs to also read portions of the cell too.
For example. Google should read the tab and display the words "Internet." But if the cell has 123Google, it too needs to output the same word "Internet."
I had a friend get me started using a Findreplace function, but when just one since cell is changed it takes 20-30 minutes it displays "Calculating (8 threads): 9%"
I'm not looking to simply do a count, but actually display text from the other tab. 2.jpg 1.jpg
The first tab I would put in American..., then based on the second tab, it will show "Flights."
The formula I have is the below. I didn't make that up, but was supplied to me.
=IF(LEN($C2)=0,"",XLOOKUP(FINDREPLACE(RXREMOVE($C2,"#[0-9].+",TRUE,TRUE),"*","\*"),Data!$A:$A,Data!$B:$B,$H5,3,1))
Then in a hidden column is the below:
=IF(LEN($C2)=0,"",IFERROR(VLOOKUP($C2,Data!$A:$M,XMATCH("Look up data",Data!$2:$2),FALSE),"ADD EXPENSE TO DATA TAB"))
I think it works, but each time I insert any text, it goes in the super long calculations: "Calculating (8 threads): 9%"
Hmm that is probably above my Excel skill level. I'm guessing it's a huge spreadsheet, so the xlookup and vlookup scans take a while.
The countif is to detect the match, not do an actual count. The problem is that IF doesn't accept wildcards. Sounds like you need to reference other cells in your search, not just a text match.
From the link I posted earlier: https://www.ablebits.com/office-addi...-partial-text/
Instead of hardcoding wildcard strings in the formula, you can input them in separate cells, say D2 and F2, as shown in the screenshot below. Please notice that these cell references are locked with the $ sign so that the formula copies correctly to the below cells:
How many rows and columns are on each sheet? How many of the rows and columns are in the lookup tables? The calculations should not take that long. There may also be alternative ways of doing this that would be much faster.
563 rows should not take that long to do anything. If you can create a sample workbook with any sensitive data removed or replaced with non-sensitive entries, and attach it, that might give me an idea of a faster method to perform the task.
Attached is the file (in zip format). When you open it, I have it with the Formulas Calculations Options set to manual. When you change it to Automatic, then try going to the data tab and add a new item and let me know what it does on your side.
Reply With Quote
