User Profile
RobG92
Copper Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Writing a formula to return a specific value from a table.
This seems to do the trick, but in my example, it unfortunately doesn't cover the full breadth of variations in categories. The colon might appear twice in some category titles. Like "All Stars: Monster Edition: The Shark's revenge" for example. Your formula doesn't seem to work for this condition. But if I change the reference to accommodate, it works out. What would I need to do to make this work for the above example?1.4KViews0likes1CommentWriting a formula to return a specific value from a table.
Hello community, I'm having trouble making a formula, maybe I'm just over thinking it so I'm here to ask for help! I have a excel document with two sheet tabs: See attached for example. Program Table Category Table What I need I need a formula that will look through column B, which contains categories that show up at different times, on the left. Find the category in each cell and return the corresponding ID found in the "Category Table" tab. The trick here is that some of the cells will contain specific text such as "News" or "Toon" as well as extra text. So, I'm really hoping for a formula that will find that similar text in a cell, and provide the ID for the program. For example. If it says "Toon: revenge of the villain" I want the same ID for "Toon:" to be applied. Thank you, One of my attempts is attached.1.5KViews0likes3CommentsRe: Formula to automatically move to a new column based on date.
HansVogelaar I like this formula, how would I modify this formula for a third and fourth category to sort? Example from the sheet attached. I have another set of data where, out of the sum collected with your formula, I need the singular sum total of items that correspond to a certain language and/or type. Which is why I used SumIF31KViews0likes1CommentRe: Formula to automatically move to a new column based on date.
HansVogelaar If I have Today() on A1, it produces today's current date. In a table, I have a set of data with dates as headers and numeric data under each date. I need help figuring out an appropriate formula to automatically sum data based on the date in the header. And that the date in the header needs to match the date in A1 ("TODAY()"). I was using a SUMIF Formula to Sum based on other criteria, but I have to adjust range being referenced in the data to reflect the data in the column under the present day represented in the header. Hope that makes sense.32KViews0likes4CommentsFormula to automatically move to a new column based on date.
Hello everyone, attached is an example of the data I'm working with. What I'm having trouble figuring out is a formula that will move over automatically as the date changes with the =TODAY(). I currently use =SUMIF() to provide a total of the range under the date. But I need to shift over the formula's criteria references every day. This formula will help with other parts of some work as well and would go a much longer well. Name Category 10/1/2020 10/2/2020 10/3/2020 10/4/2020 10/5/2020 10/6/2020 10/7/2020 10/8/2020 10/9/2020 10/10/2020 Black Dark 50 3 44 32 0 22 150 2 38 0 Thank you,Solved33KViews0likes7CommentsFormula Assistance: Maybe I'm making this hard...
Hello everyone, I have a spreadsheet that I'm making that relies on some given data from a database that needs to be translated properly. Refer to the attached document for an example of this and some formulas I had dabbled with: (its all hypothetical) What I want is a formula that will ultimately apply a value to an entire row based on the "dates completed" and the "affiliate name". What we used in our Macro: =IF(RC[1]="Worked",IF(OR(ISNUMBER(SEARCH(RC[-3],RC[3])),ISNUMBER(SEARCH(RC[-3],RC[4])),ISNUMBER(SEARCH(RC[-3],RC[5])),ISNUMBER(SEARCH(RC[-3],RC[6])),ISNUMBER(SEARCH(RC[-3],RC[7]))),"Worked","Recertify"),"") There are similar iterations but here is what its supposed to do: When we retrieve the data it already tells us "Worked" or its blank, meaning it hasn't been worked. Since its not worked it doesn't need a status. But if it has been worked it needs to be labeled as worked, but every once in a while there is something that needs to be re certified because we see in our data that its been retrieved multiple times from other affiliates. But the data does't say that, it just says "worked". Instead we want the formula to find that it needs to be re certified using columns 10,11,12,13,&14 that will have the necessary data. So, If the cell says "worked" then find the "Affiliate Name" in the "Dates Completed" adjacent cells. If it isn't there, then change the value to "Recertify" If it is there then keep it as "worked". Then I need a way to apply this formula to an entire column of cells and possibly into a Macro. Driving me up the wall trying to figure this out... Appreciate any assistance.473Views0likes1CommentRe: In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
Here is a formula that is close to what I mean: But it keeps applying it to all of the cells and not the adjacent cell in the same row: "=IF(COUNTIF(R1C[-3]:R[-1]C[-3],RC[-3])>0,"Repeat Tim","")"11KViews0likes0CommentsRe: In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
So in the original Experiment attached to the original post. We have this formula: Application.CutCopyMode = False Range("K1").Value = "Note" Range("K2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTIF(C[-2],RC[-2])>1,""Multiple times"","""")" Range("K2").Select Selection.AutoFill Destination:=Range("K2:K2000") The person who originally made it up, I guess, decided to use R1C1 reference style. So I guess what I'm asking is how do I apply your formula to regular reference style or convert your formula to R1C1. Also whats happening when I apply your formula is that its giving me an error when running the Macro. application defined or object defined error. The idea behind the formula is that it should be able to apply to an entire column of active data (it will stretch between hundreds of rows) find the duplicates, apply the "Again" or "Multiple Times" to that specific item in the row under the Notes Column, column I. The R1C1 puts itself in column K and applies a formula that reaches over to apply the formulas. in another column. But We are looking to enhance the formula, essentially, and instead of marking them ALL multiple times...we want to mark singular ones, as the second or third occurrence etc... If that makes sense.11KViews0likes1CommentRe: In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
I'm not an expert by any means in Excel Macros/VBA. We just had an existing Macro and I was hoping to manipulate it to do this for us. We need it to copy and paste in the column but what you provided isn't working...not sure If I'm doing it right or not11KViews0likes4CommentsIn a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
Hi all, Attached is a spreadsheet that gives an example of what I'm working with. In it, I'm trying to create a formula that searches all of column F for the exact same tag numbers. And if it finds one it will state "Multiple Times" in Column I (Notes). And if the same tag is found a second time, then it will apply a value to the second, third (and so on) occurrence as "Attached" in Column H under Label. The formulas I was working with in the past looked like: =VLOOKUP(OFFSET(F1,0,100,1),F2:F2000,8,FALSE) or using IF & MATCH instead of VLOOKUP. They both provided similar results but I'm still stumped on it. The goal I'm trying to reach is that when I run my Macro, I want it to find these duplicates and apply the "Multiple Times" in the Notes column to that corresponding animal with that tag. And then I want it to recognize that there are 2nd(later) times where it occurs again and to let me and my employees see this so we can take care of the animal right there instead of later. Anyone got any ideas?12KViews0likes8CommentsRe: How to remove conditional format in specific cells via Macro?
You're on the right track!! I am not proficient enough to understand those "Dim" stuff. But I tried to remake it for my use and it worked for one row but stopped there. On any given day we might have 3 or more labeled "Rework" in a list of hundreds of rows. But it worked for one row specifically. Is there a way to get it to work in the same vein as conditional formatting? Whee every time a new cell in column H is labeled "Rework" it clears the formatting for the row?9.1KViews0likes1CommentHow to remove conditional format in specific cells via Macro?
Hello, I'm trying to figure out how to remove conditional formatting in specific cells. We have 9 columns. In Column 8, the header is called "Status" Among the status' there will be the occasional value added manual called "Rework". I have other conditional formatting throughout the document and when something is labeled "Rework" I need it to override Cells A-G (Ignoring the formatting to H) and override I so that we can color those cells specifically. On a given day we might have 5 out of a hundred so I'm trying to figure out how to do this in VBA for our Macro, if possible. Any ideas? Help is appreciated9.2KViews0likes4Comments
Recent Blog Articles
No content to show