Hey,

I'm somewhere between beginner and a little next level for Excel. So excuse my lack of knowledge.

I am trying to match the 'Unit Price' column in the main sheet, depending on what Item I select from the drop-down in Column C (dynamic - depending on the category I choose in column B).

I was able to do it for one category, by using 'IF' conditional formatting using Named Tables, however, I realised that doing it for all categories would be too complicated and there has to be a better way of doing it! I tried searching but got different answers that got me confused and still didn't work.

Raw Data Sheet photo in comments

Main Sheet

Guys, is there a way to use the 'Clear' and 'Reapply' filter options in Excel on protected sheets? I enabled the autofilter in the protection options, and I can apply and remove filters in each column individually, but I can’t clear all filters at once when the sheet is protected. Is there a way to do that?

Hi guys, I'm looking for some advice on how to create this spreadsheet in the most professional way possible. I work for an electronics company and we receive defected products everyday that need to be sorted and repaired. I want to create a sheet that includes the date of arrival at the factory, which client, the issue, if it's still under warranty, etc. Afterwards I'd like to create a pivot table(i have very little experience) that can use this data to see trends in what issues are arising the most, which products are being returned most often, etc. Could anybody give me advice on where to start? Thanks

https://preview.redd.it/asjkl4z5kxqd1.png?width=1627&format=png&auto=webp&s=e44921e2da6f79361daa7cadcfe50e82627edfb7

I used to have a lot of skills building charts in Excel. But I was for long years without using them and now Im back to it and having a huge headache trying to remember all functions and solutions. Can enyone help me with a solution to a table of data like this? I need to show all this data in a chart. The preference is that the data in light blue is displayed as bars and the data in white should be displayed as a line (they are %, but I noticed that if the numbers are set as %, the chart would not show anything at all). The data in the last column should also be showed as a line as it is also % (the single cell above has the total amount from which the percentage was calculated). I am quite lost here and working on it for one day trying different things and nothing works as expected. There are titles in the rows and columns. I tried pivot-chart and it was looking promising, but the outcome is a mess. Appreciate any help!

Hi all, I'm driving myself crazy trying to do something that I feel SHOULD be really simple, really hope someone can assist.

I have a set of data about what products various customers have bought and in what configuration they bought that product, along with the contract number. I'm trying to create a summary as a string of what that customer has bought.

Simplified data below

|| || |Contract number|bundle configuration|bundle items| |00001|configuraton 1|bundle name| |00001|configuraton 1|Option 1| |00001|configuraton 1|Option 2| |00002|configuraton 2|bundle name| |00002|configuraton 2|Option 1| |00002|configuraton 2|Option 2| |00003|configuraton 3|bundle name| |00003|configuraton 3|Option 1| |00003|configuraton 4|Bundle name| |00003|configuraton 4|Option 2|

I have a page where I have the contract numbers, and next to it I want to create a one cell text string summarising the bundle configurations and options that each contract contains.
contract number

|| || |Contract number|summary string| |00001|Configuration 1, bundle name, Option 1, Option 2;| |00002|Configuration 2, bundle name, Option 1, Option 2;| |00003|Configuration 3, bundle name, Option 1; Configuration 4, bundle name, Option 2; |

I feel like this should be simple, but I can't for the life of me make it do it like this. The closest I have got is:

=TEXTJOIN(", ",,IF(A2=datasheet!A:A,UNIQUE(datasheet!B:C), "")) 

where A2 is the contract number I'm searching for, but that's giving me

|| || |contract number|ugly summary| |00001|configuraton 1, bundle name, configuraton 1, Option 1, configuraton 1, Option 2| |00002|configuraton 2, bundle name, configuraton 2, Option 1, configuraton 2, Option 2| |00003|configuraton 3, bundle name, configuraton 3, Option 1, configuraton 4, Bundle name, configuraton 4, Option 2 |

The Configuration details get repeated in the string, which is ugly and makes the resultant data really difficult to read. The real data set has far more repeats per config and the bundle configuration is quite long, so it makes the result un-readable. Also each contract may or may not have several bundles in it.

I need something that dynamically looks to see if column A matched the known contract number then, looks to find which column B data matches each other and if it does, append the bundle configuration once to the items in the bundle, without having to tell it what the value in column B will be and I am totally stuck!

A friend of mine and I were thinking of going just to watch and was curious if anyone here has been? Trying to figure out where to start with going. Definitely not competing.

Does any one have any good recommendations for FREE Cap table and Waterfall VC returns analysis courses? I would like to start with free courses and move up to more comprehensive ones, like Foresight, if I don't find those sufficient.

Thanks for any help!

I would like to compare 2 spreadsheet where the rows and columns don't align (or they have information in different order to each other) and there might be values in 1 that's missing from the other.

Sheet 1:

Sheet 2:

Sheet 3, preferred outcome, something like sheet 2 minus sheet 1 + add rows & colums not in sheet 2:

^{I hope I calculated all correctly}

I feel like it should be very easy with index-match function + some sort of if(countif) to check if there are some rows & columns missing from sheet 2. But I just can't figure it out.

Office365, version 2407, if that makes any difference

I am working at a factory and we scan barcodes and transfer them to excel our barcodes are 20 digits. I need to count how many same barcodes there are. If equal to 2 green, if not red. I did almost all of it and it works the problem is some codes first 15 digits are same. Ex. 00000000000000001234 and 00000000000000004321. I use ıfcount formula to count but it seems to check only first 15 digits so if I scan the two number in my example it will count 2 of same code when actually there is two different codes. How can I bypass or override this rule.

Hi all,

Had a quick question as I had a quick question. If I had :

Cell A2 = 23:57

Cell B2 = 00:05

How would I get the answer to = 8 minutes for the difference between the two times?

I've tried MOD ((A2-B2, 1)*1440 &

=IF(B2<A2, (B2+1)-A2, B2-A2)*1440

I have tried google which only tells me =B1 will copy A1. This is annoying if I need to make a change down the road and have to go back to the original cell to copy the formula. I want the formula to more or less be copy-pasted automatically in B1 whenever I make a change in A1. Is this possible? It's in tables.

Hello,

I have a list sent to my by a company my clients purchase from. I sort my list and color code them to my clients. My issue is I want to be able to scan a barcode from a box a receive, and for the the new cell in which the input barcode went it to replace itself with the exact barcode from my already colored list. Each barcode is unique. I will be able to sort through my items this way faster this way instead of manually finding barcode matches. I simply want the barcode input to swap from the original list so I can verify the color and move on to the next and so forth.

Thank you.

Issue

This has been confusing me for a while. Writing out the title itself was difficult enough without being confusing.

I have multiple cells of text that include numerous genres, and not in any specific order. For example, the cells can look like this:

_____________________________

Musical, Comedy, Music

_____________________________

Music, Drama

_____________________________

Adventure, Musical

_____________________________

I am trying to figure out a COUNTIF function that counts any cell with the "Music". I do not want to count cells that contain "Musical" and not "Music".

In this example, the count I am trying to get is 2 (2 cells contain the genre Music)

Because the word "Musical" contains the word "Music", I cannot figure out a way to count just "Music". And because there can be other random text within the cell listed in a random order, I cannot filter out by cell size.

Attempts

• =COUNTIF(Data!K2:K5000,"*Music*")
  • Counts cells with Music, Musical, or Both
  • Count returns 3
• =COUNTIF(Data!K2:K5000,"*Music*")-COUNTIF(Data!K2:K5000,"*Musical*")
  • Does not include cells that contain Music AND Musical
  • Count returns 1

Solution

Currently none. I cannot even figure out how to word a question that searches for this solution.

Edit1: Thanks for all the comments. I will try to reply to them and attempt the solutions when I am out of work.

I built a piece of software that allows a user to upload an excel file that was exported as a CSV to a web portal for mass data uploading. Everything was working well, but I've gotten some complaints that the website isn't allowing them to upload anymore because the file size is too large, keep in mind, these reports should never ever have enough data to go above 1 MB, but for some reason, one of the reports is being downloaded with a file size of 17 MB. When I download the file and then resave on my computer the file size drops to 11 MB (still too large), but I am completely lost. I don't want to spend a day creating a file-shrinking workaround, any advice would be greatly appreciated.

I was looking for a formula that compares the values of a row depending on its repeating label.

E.g.

Target 1 / 2 / 3 Actual 1 / 2 / 3 Target 1 / 2 / 3 Actual 0 / 1 / 2 Target 1 / 2 / 3 Actual 1 / 1 / 3

I'd like to be able to count the quantity of line items in which the actual has met or even surpassed the target without having to specify each line item (since I have 32 line items, each with multiple rows such as target, forecast, and actual).

I keep googling to no avail, and was wondering if there's an answer to my question here. Version 2021

It would be easy if my excel spreadsheet would automatically update prices of products if i could connect this with my supplier but i don’t know if this is possible?

https://postimg.cc/gallery/1Ddw6Vm

I have a spreadsheet where I am tracking best of 3 games, currently (you'll see in the first image) I am tracking this as just a total of wins and losses within the game (each match consists of 2-3 wins), I would like to split that up to track whether I won or lost in round 1, 2 and possibly 3.

I need a better formula to return whether each game is a win or a loss when I move to tracking how many wins in a match. Image 2 will show you the janky formula I'm using now.

The main issue that I would like help with is a better way to track my Win%. Currently I have a running total (i usually hide the columns), of wins and losses and just divide it by the total games. When I export the data into a pivot table it is taking the numbers from each row and adding them all up.

Edit: Including image of Pivot table (work in progress) https://postimg.cc/S2jCnnx1

It should be noted that each row is its own game.

The meta% column can be ignored for the purpose of this post.

Creating a template sheet at work that enables users to screenshot document pages with Windows+Shift+S and hit a button in a specific cell to paste their screenshot from clipboard.

I can’t for the life of me figure out how to access the clipboard via VBA, and can’t find reliable sources to assist.

Has anyone here worked on a project where they’ve successfully extracted clipboard screenshot data, and if so, how did you achieve the desired result?

In the top chart below you can see there's an empty cell in the 2/28/2024 column. This is preventing the data for 8/28/2024 (6.2) from displaying. In the bottom chart I've entered 0 in this cell and it allows the 8/28 data to display, but the 0 value distorts the rest of the chart (plus, 0 isn't the same as no value, which is the case). What can I enter in the empty cell to allow the 8/28 data to display, but without the distortion? Thanks!

Btw, the problem is solved if I add data after 8/28, but I'd like to solve it now without waiting for the next data point.

Edit: this is Excel 365 on Windows 10.

https://preview.redd.it/gwdbfk3iyvqd1.jpg?width=1583&format=pjpg&auto=webp&s=bac2d89f52ef3a44646424c75773c82bd48ab832

Hello,

I've been trying to combine two datasets into one easy-to-digest format.

As an example of what my ask is, let's say I run an animal breeding agency.

First, a listing of animal breeders, the name of the animals sold, how much the animal sold for and where the pet resides:

Second, a listing of breeders who are agreeing to breed more pets, their contract number, where the pets will be sent to, and the date they need to breed the pet by:

I want to find a way to get these two tables into one condensed form (like a PivotTable) that depicts all breeders who have already sold pets, which city their pets are located and their average sales in that area. On top of that, I want this table to also show their adoption contract if they have one, the location where they're pledging to send their adoption, and their breed-by date.

My actual data set is a lot longer than this and contains a lot more data that needs to be expressed, so some way to condense the data and use a slicer is necessary. For example, I want to filter for Robert, see his average sales for his dogs sold and that he's planning on breeding two more dogs in the coming year.

Essentially, I need a better-looking version of this.

https://preview.redd.it/drm4xrzi4uqd1.png?width=778&format=png&auto=webp&s=7e8e1b5a005e071c2e9dbd4090dc99a61b2cd5bc

Because my data sets are different in length and point to different fields, I can't simply combine the tables and create a large pivot out of it. Inputting dummy rows put them all in the same table would skew calculations for analysis downward, and I need a dynamic table that can search and update on-the-fly, so hard-coding calculations is out of the picture as well.

I tried my hand at PowerPivot, but it hasn't worked for me. Using a dummy table doesn't get the outputs I need because the only related column is the name of the Breeders and they point to different data sets and lengths.

Some advice on if this is doable using Excel or PowerPivot would be nice. If not, is there any way I can integrate these tables into a pivot for easy readability?

I would like to obtain the number of grade range the class obtained for my exam. For example: I have 90 students and would like a breakdown of how many obtained 90-100 (A), 80-89 (B), 70-79 (C)... and so forth. I have already obtained the easy calculations such as mean, max, min... is there a calculation that can provide with it? Thank you

Update: thank you for your teachings! I will apply your guidance for future reference.

Hey guys, new here.

Hoping someone has an easy to understand way of doing this, I’m not great at excel!

I have two worksheets, one has 1933 rows of email address in it, and one has 2587 rows. Not each row in this sheet has an email address in it. Many of the email addresses will match across both sheets but won’t be in the same row in their respective sheet.

I need to somehow compare the two columns of email addresses, to see how many of the 1933 emails are no longer included in the 2587 email addresses. Does that make sense?

Thank you in advance 🙏

So recently I downloaded a Python (Boardflare) Add-In to my Microsoft Excel for Mac 2024 and have so far been playing around with it. It’s relatively new (came out early September 2024) and was wondering if anyone has tried it so far? I’m only able to use two functions: =BOARDFLARE.PY & =BOARDFLARE.PY.ARRAY, and am able to add floats and integers, but haven’t figured how to add strings or any further features past that. I talked to one Boardflare reps and they said it’s based off Python 3.12 & Pyodine Runtime. If anyone has any more info on this let me know thanks!

What I Have (NO merged cells)

Hello!

I acknowledge this may not be possible but I wanted to put this out there to see. I am studying anatomy and there are a lot of muscles that have the same action, nerve, or artery. I want to filter muscles based on a single action, nerve, or artery (I.E Only see muscles connected to the "Cranial Gluteal n." OR only see muscles that "Rotate the limb medially").

However, ALL the respective actions, nerves, and arteries associated with an individual muscle are contained within a single cell. Thus, I cannot filter based on a SINGLE trait that muscle has. If possible, how can I format this document to only filter based on a single trait?

Thank you!

I'm trying to apply a filter to a dataset based on a handful of key strings, but power query is being very selective in what the filter does and doesn't catch.

I'm filtering with:

each Text.Contains(Text.Lower([Model Number]), "gd95") ...

However, this ends up capturing one value (GD95-6223) and missing another (GD95-6224). I have no leading or trailing spaces in the data and have no idea why this might be so screwy, any input?