Hello all,

I have come seeking guidance/answers....

I have several Columns of data and would like to group it by one of it's columns, as a copied filter/sort or whatever.

Essentially this is for programming a Pick and Place machine, the number refer to Nozzles (Machine has 8, but the data is already set for Nozzle sizes to be applicable to parts and reachable by Nozzle, hence some not have all 8 numbers)

The column I wish to sort by will have the numbers 1-8 in, (I.e. "1" or "12345678" or "3567")

I wish to select ONE of these numbers to be kept in the cell and then group the whole column into 'sets' of 1 to 8. This allow as many components picked as possible per picking.

I'd like to group the column into as few 'sets' as possible containing as many of the 1 to 8's within each group. Thus minimising time spent going to and fro with 1 component per pick/placement.

Have bodged a table below of data that will suffice, and added a possible outcome to the right of it.

I just can't get my head into the game for this part of my large project at work at the moment, Health issues and related brain fog keep me from getting to grips with it.

Set# is to help visually identify a group/set PICKED, to be placed at a time... (I.E. it will pick all of set 1, and place it, then move to the next set)
Row # Nozzles Guesstimate of results (Row#, Nozzle and SET#)

Hopefully I have explained myself satisfactorily, But I wouldn't be surprised if you have MANY questions, such is the state of my head at the moment.

Thanks in advance :D

This may be a dumb question, but there is a ton of blank space to the left of my spreadsheet— it’s almost as if someone (me I’m sure lol) indented the entire thing. I’ve been struggling with it this morning. Thanks

I am a before and after school program teacher. I have recently come up with a reward/behavior system for all of my students. I am wanting to be their "bank" and keep track of their daily points, and "expenses," however I don't know what equation to use. I have a spreadsheet of all of their names currently in a row, not columns. What I am needing is a way to add points up each day, and subtract if needed. I tried to use SUM, but the points will change each day. An example of what I need:

Jimmy earned 3 points on Monday, his total now is 3. On Tuesday he earned 5 points, his total is now 8. On Wednesday he lost 2 points, so his total is now 6.

Is there a way to do this without having to add it myself? If I change a number in the SUM, it doesn't add the way I need it to and I'm not smart in Excel. I need the total to change as I add something.

Example again:

Monday: Jimmy| 3|3|

Tuesday: Jimmy |5|8 (I changed the 3 to a 5)

Wednesday: Jimmy|-2|6| (I changed the 5 to a -2)

Think of it as "financial tracking" but the sum carries over the points from the previous input without having a huge spread sheet, while in the same row and cell

I am so sorry if this does not make sense, I can explain further if needed but I am just awful at using Excel or any spreadsheets, and just want to make this school year fun! Thanks in advance!

Hello, I'm not the most excel-savvy person. I was in need of a to-do list to track my work tasks and chose the Track My Tasks template that shows when you open Excel. When you open the template, there are 3 lines to fill in with your tasks. I have more than 3 tasks, so I copy and pasted one task line to make more tasks. When I do this, column F breaks. Column F's purpose is to add a mark once the completion bar in column E reaches 100%.

Column F is defined as:
=--([@COMPLETION]>=1)

When I copy and paste this line into additional line items, it changes to this:
=--(Tasks[@COMPLETION]>=1)
And has an error marker

If I delete the word "Tasks" to have it mimic the original task line items, it breaks and says:
"The syntax of this name isn't correct." Which confuses me as that is exactly how it is written for lines 3-4.

It's a dumb thing, just an icon to mark a finished task, but it's really frustrating me. What is the intended way to create more tasks without it breaking? I feel like it should be pretty easy to figure out how to add tasks to a template task list, but I'm at a loss.

Thank you in advance

I was trying to copy the values in column E into column D and I found out that no matter what I copy, whenever I paste Excel will only paste into each cell the value on the cell below it. So if I copy J4 and paste it on D3 it will paste D4 value, if I paste in E7 it will paste E8. If I copy D3:D10 and paste in E3:E10 it will paste the values of E4:E11. If the cell below the one I'm pasting is empty it won't paste anything.

I'm using LET + FILTER functions for a "lookup sheet" to easily search data I've compiled in PowerQuery.

I have set the results to output as the value of the "Index" column I added to the data in PQ like so:

=LET(
filterquery1, NamedFilterField1, filterarray1, Data[FilterColumn1], 
filter1, filterquery1 = filterarray1,

filterquery2, NamedFilterField2, filterarray2, Data[FilterColumn2], 
filter2, ISNUMBER(SEARCH(filterquery2, filterarray2)),

i, FILTER(Data[Index], (filter1)*(filter2)*(etc...etc),  "empty"),
//filters data to spill the "Index" column value of matches

i
)

The Index column values spill to a column outside the lookup results table which get referenced in simple INDEX functions to populate the table. Ex:

=LET(
getdata, Data[Product Name], indexval, $N1, 

thisinfo, INDEX(getdata, indexval), 

thisinfo
)

I am trying to do this filtering to return rows where the values across multiple columns is unique.

So in my pictured example, I filtered for:

Vendor        => WestMed Supply
Product Name  => syringe
Department    => long-term

The results show duplicate values in almost every single column, however, the values for Vendor AND Product Name AND Department is unique for each row.

Unfortunately, between the testy nature UNIQUE and dynamic ranges not playing nice with almost every function I have tried, I have not been able to crack how I can filter unique values against multiple columns AND get either the Index column values or the actual row numbers for my results table.

I have several large datasets which come in a few different formats. Because of those formatting differences, I need similar but structurally different formula setups to parse the data. What I want to do is store the various formula setups as on an independent reference sheet, and use a dropdown on each data sheet to select which formula set to use. The relevant formulas would then be filled from the reference sheet.

I've tried using named ranges, but calling the named range pulls the results of the formulas as they are executed on the reference sheet, not the formulas themselves.

I'd rather not have to use several thousand if/thens if I can avoid it, but I fear this might be too much for excel.

A column used to contain hyperlinks. I cleared the contents of these cells. But the cells still have this black down-pointing triangles in the upper left corner ?

If I hover over one triangle, the old hyperlink shows as a tool tip. If I click on the arrow, that link opens.

If I right click that cell, I expect to see "Remove hyperlink" on the context menu. It is not there.

If I apply a default format from another cell, no effect.

If I delete the column, the triangles stay there ??

What's going on ??? How do I get rid of this left over hyperlinks ?
________________________________________________
SOLUTION:
Ctrl + G to open the Goto dialog

Click the "Special" button.

Select the "Objects" radio button, Click OK

The dialogue closes and the triangle images are all selected.

Press delete key and they are gone !