So whose good at power automate/excel/SharePoint? Basically I have an excel file in SharePoint. When I add new rows to that file I want an email to be generated to a group of people basically saying "hey this has been updated, have your people update the file with the missing information".
excel dudes please please help. this is beyond my googling formula searching skills Let's say I have a sheet with thousands of orders in a similar format shown below. I need to find all orders that contain coreitem1 but do not contain addon3 or addon4. In the list below, only order 222 matches.
Option 1: chatgpt Option 2: can you pivot table and then filter based on your criteria? the easiest way is to toss it into python, use df.pivot and then filter Spoiler ChatGPT response To find all orders that contain "coreitem1" but do not contain "addon3" or "addon4" in Excel, you could use a combination of formulas to achieve this. Since you have a large dataset, the most efficient way is to use helper columns to flag the desired conditions and then filter on those flags. Here's a step-by-step guide: Flag Core Item: Create a helper column "CoreItem1_Flag" to flag rows with "coreitem1".Use the formula: =IF([@Items]="coreitem1", 1, 0)Drag this formula down the column to apply it to all cells. Flag Addons: Create two more helper columns "Addon3_Flag" and "Addon4_Flag" to flag rows with "addon3" and "addon4".Use the formulas: =IF([@Items]="addon3", 1, 0) and =IF([@Items]="addon4", 1, 0)Drag these formulas down the respective columns. Aggregate Flags per Order: Create two more columns "Order_Has_Addon3" and "Order_Has_Addon4" to aggregate the flags per order number.Use the formulas: =SUMIFS([Addon3_Flag], [Order#], [@Order#]) and =SUMIFS([Addon4_Flag], [Order#], [@Order#])Drag these formulas down the respective columns. Final Filter Column: Create a final column "Filter_Criteria" to identify rows that meet your criteria.Use the formula: =IF(AND([@CoreItem1_Flag]=1, [@Order_Has_Addon3]=0, [@Order_Has_Addon4]=0), "Match", "")Drag this formula down the column. Filter for Matches: Finally, apply a filter to the "Filter_Criteria" column to show only the rows with "Match". Since you have thousands of rows, you could also consider using a PivotTable to help summarize the data or use Excel's advanced filtering options to handle the criteria directly. However, the above method should give you a clear visual indication of each step in the process and is generally suitable for most users. If you want to do this programmatically, you could write a script in VBA or use an advanced tool like Power Query to automate these steps. If you are using Python for this task, similar logic can be applied using pandas.
I'm sure there are other ways to do this and pivoting it seems like the absolute easiest. but the way that came to mind for me is as follows: 1. add a "1" to every row in the order detail sheet so you can sumifs the data instead of if statements 2. copy the order# and remove duplicates to get a list of unique order numbers 3. copy the items and remove duplicates to get a list of unique items (though i suppose you could grab just coreitem1, addon3, and addon4 here if this is a very limited scope data pull) 4. run a sumifs based on the "1" counter we added + order# + coreitem. in my case, it's =SUMIFS($D$4:$D$22,$C$4:$C$22,G$3,$B$4:$B$22,$F4) 5. add the counter check column, which is just coreitem1 minus addon3 minus addon4. in my case, it's =G4-J4-K4 for the first row 6. filter or pull just the order#s where counter check is 1 (no addon3 or addon4) or 0 (just one of addon3 or addon4)
Another fast way would be filter on addon3, addon4. Copy those order ids to a list on a new sheet and remove duplicates.These are your knokckouts. Unfilter and vlookup order numbers against that list. Filter to core item 1 and n/a on the vl and that would leave you with order numbers that contain core item 1 and also came back clean checking for addons 3/4
DollarBillHokie Handcuffed racer you guys rock, thank you much for the solutions. I ended up going with racer's solution because it was the easiest for me to understand this morning. Was able to pretty quickly narrow down a list of almost 10,000 orders to 90 that matched the criteria I needed. Now more work to review these 90 for potential problems.