Got it, that makes sense. You probably wont know the answer to this, but are those heights stored as values in excel or text? If its text, they will need to be converted to values and then easily enough we can cut out the ones you aren't interested in. I will PM you.
The formula below has worked for me for other sheets, but this does help me right now because Column N contains duplicates that I don't care about. I only want to see where Column N matches any value in Column O =IF(COUNTIF($N$2:$O$58293, $N2)>1, "Duplicate", "Unique") How do I build a formula that tells me which values in Column N match up with a value in Column O. And it has to be *any* match like, this example: Column N = 33333 Column O = 33333-4444 That needs to classify as a match
I don’t have an answer but this may help https://www.xelplus.com/if-function-with-wildcard-partial-text-match/
Thanks but not smart enough to figure that out. I thought maybe this one would help but I can't figure out how to apply it to my need =IF(OR(ISNUMBER(SEARCH(“AT”,A5)),ISNUMBER(SEARCH(“DE”,A5))),”Europe”,””)
put this in A1 and fill down =IF(NOT(ISNA(VLOOKUP("*" & B1 & "*",$C$1:$C$4,1, FALSE))),"Duplicate","")
Thanks Henry Blake I feel like I'm getting closer. Perhaps it would help to show you guys a sample dataset See attached image - I want to find any zip codes in column B that match the list of zip codes in column C. I had created column D thinking I'd throw a formula in there to accomplish this, but haven't figured it out yet.
does the match have to be specific to the row or can the matching value be anywhere in the searched range? At first glance, I think you’re going to want to make a key and use xlookup. Or level up and download a trial version of Alteryx.
create a new column and use LEFT(B2,5) (then drag down) to get your first zip code column into the same format as your second one then you can just use any lookup/if function to check if column C data is contained within your new column
Yeah, left 5 or text to column by - then a1=b1 is what you want if t worried about that row matching. VL if it could be any in a range.
My biggest pet peeve with excel is that the power is inefficient. It’s way more quick and easy to do 2 steps than it is to try and perfect a complex formula in many cases.
put this in D2 and fill down and vlookup should work if you want to use that =IF(NOT(ISNA(VLOOKUP("*" & C2 & "*",$B$2:$B$12,-1, FALSE))),"Duplicate","")
Was that what you needed? You're going to want to convert those text values to numbers as well. Also to fellow excel bros, I've found XLOOKUP is vastly superior to VLOOKUP for pretty much every application. VLOOKUP being extremely brittle and cumbersome.
Fuck yes! That did it! Finally! I had to switch it around to this: =IF(NOT(ISNA(VLOOKUP("*" & N2 & "*",$O$2:$O$293,-1, FALSE))),"Duplicate","") fill down 58,000 rows and found 1420 matches yesssssssssssssssssssss this was it
I couldn't figure out how to apply your formula to what I needed. Kept getting errors. I've never used XLOOKUP before. Henry Blake's VLOOKUP worked so I'm rollin with that for now
XLOOKUP is fantastic. I use that formula so much now in place of index- match and vlookups. Try it out. You won't be disappointed.
Does a value of 11111 in N match a value of 11111 in O? Do you need that to match? I can't get that to work in my excel.. Also, the column index (-1) may need to be 1 for consistency?
I do not need that to match, but, I just tested it. I added 11111 in N and O, and could not get it to match.
In the event that you do need 11111 in N to match O use: =IF(OR(NOT(ISNA(VLOOKUP("*" & N2 & "*",$O$2:$O$293,1, FALSE))),NOT(ISNA(VLOOKUP(N2,$O$2:$O$293,1, FALSE)))),"Duplicate","")
Awesome. I figured it out now. That worked! Had to just flip a bit around and made it like this: =XLOOKUP(N2,$O$2:$O$263,$O$2:$O$263,"Not Found",2,1)
Henry Blake the XLOOKUP that DriveByBBQ posted worked. So thanks to TMB I have two solutions, both verified to have the same output: =XLOOKUP(N2,$O$2:$O$263,$O$2:$O$263,"Not Found",2,1) or =IF(OR(NOT(ISNA(VLOOKUP("*" & N2 & "*",$O$2:$O$293,1, FALSE))),NOT(ISNA(VLOOKUP(N2,$O$2:$O$293,1, FALSE)))),"Duplicate","" You guys are awesome thanks. Gonna try using XLOOKUP more.
One problem with this is 32976-6800 in Column C will not match with a 32976 in Column D. I'd have to first trim Column C zip codes like Handcuffed said =LEFT(C2,5)
I'll also recommend the excel crash course linked below from Wallstreet prep. Wallstreet investment banks make their interns take this course as a prerequisite. It's an incredible value at $39, lots of hacks (where I picked up XLOOKUP), the instructor is concise, and you can keep it as a reference. I'll still flip back to it if I'm stumped building out models. https://www.wallstreetprep.com/self-study-programs/excel-crash-course/
Yeah, so I inserted a column, did a text to column data parse using "-" as the delimitator and expanded the return array to include columns B and C. It's set to search first to last so it returns the first value it finds. If you're playing with zip codes, I'd highly recommend downloading a free trial of Alteryx Designer. It's a geospatial, SQL, data analytics, workflow automation platform that is all point and click drag and drop. It's the future. Alteryx zip code tool https://help.alteryx.com/current/designer/us-zip9-coder-tool
In a lot of cases it seems like leveraging complex formulas takes more time and effort than using two or three basic formulas for your average user without advanced skills. I love me the shit out of some macros, tho.
Yep. Helper columns seem so klunky, but often it's easier for someone else to understand the spreadsheet if they're used. For calcs that aren't too involved, I like writing custom functions in VBA and calling them in cells. Can be pretty powerful. I have not used it much. I will check it out.
I agree with that. I also think most of that headache could be bypassed by properly organizing the data on the front end and pivoting or w/Power BI but the gen x'ers and boomers can't wrap their minds around the concept of having a data input and deliverable output.
Not really, and I'm not a VBA expert. I use stackoverflow.com all the time for programming questions. If you have programming experience, you can use that and the hints that the VBA editor gives for the Excel DOM. Also, I'm sure you can find online tutorials for any level of experience. One approach I like is to record a macro and take parts of that to piece together what I need. I don't use VBA all the time, so it helps with the syntax for basic stuff that I forget.
My work laptop recently lost track of excel as a program or something and deleted all the extensions from the defaults, and it would not stick when I tried fixing it. Finally got that resolved and was good for like a week or two and now .xlsx keeps reverting to Microsoft Excel instead of just Excel for its program, which are apparently different. It’s a little bizarre and really stupid. Getting real sick of saving everything and going to file-open.