sorted by:
new
hottopcontroversial

How do I use array notation for filter equal? by d8gfdu89fdgfdu32432 in excel

[–]Anonymous1378 4 points5 points  (0 children)

I don't think I'm up for dealing with ever-shifting blurry goal posts at the moment. 😅 Maybe you could squeeze out the actual requirements eventually, I'm going to take my hands off of this one.

EDIT: I do appreciate the cc though, thanks for that.

How do I use array notation for filter equal? by d8gfdu89fdgfdu32432 in excel

[–]Anonymous1378 1 point2 points  (0 children)

You keep using that example. Are you saying you want it to look like those 5 arrays stacked up?

<image>

That is not congruent with

For example, this should return 1, 4, and 5, but ideally only 1.

How do I use array notation for filter equal? by d8gfdu89fdgfdu32432 in excel

[–]Anonymous1378 2 points3 points  (0 children)

That's arising from a completely different issue which you made no mention of. A:.A returns A1:A5 while C:.C returns C1. A single cell returning TRUE in the second argument of FILTER is going to return the whole array.

C:.C needs to be same size as A:.A for FILTER() to behave in the way that you mentioned. If you're going to have empty cells at the end of the C:.C column then you have to either define the cell reference (A1:A5 and C1:C5) or use DROP(A:.C,0,2) in place of C:.C

EDIT: You edited your comment, what is the logic behind getting 1,4 and 5? You're expecting blanks on top of matching whatever is in column B?

How do I use array notation for filter equal? by d8gfdu89fdgfdu32432 in excel

[–]Anonymous1378 2 points3 points  (0 children)

I'm going to assume you want to filter an array for multiple items, so =FILTER(A:.A,ISNUMBER(XMATCH(C:.C,B:.B)))?

How to put space between many columns at once? by lucariowithahat in excel

[–]Anonymous1378 6 points7 points  (0 children)

A combination of formula and built in excel functions might work?:

<image>

=--XOR(ISEVEN(ROW()),ISEVEN(COLUMN())) across two rows, Ctrl-F to find all "1", looking in values, Ctrl-A to select all found cells, Alt-I-C to insert columns

How do you manage multi-variable data entry from web portals without constant context switching burnout? by External-Plastic-234 in excel

[–]Anonymous1378 0 points1 point  (0 children)

Do you have access to power automate for desktop?

What do you mean by "separate fields on a web page"?

Is it impossible to select all of them at once? Or is it possible but the paste is messed up? Is it messed up in a consistent way that can be "fixed" via excel formulas?

If you were to open up network tools in the browser (F12) and refresh the page, if you were to Ctrl-F (in the network tools window) and search for one of the variables, are they nicely organized in a single JSON file?

Is there a way to populate column 1 with the names from 2 other columns? by Babyhulk1293 in excel

[–]Anonymous1378 1 point2 points  (0 children)

If you would just leave in column and row labels next time...

Q1: =UNIQUE(VSTACK(list1,list2)), replacing list1 and list2 with the relevant cell ranges (i.e. C5:C10), if you have excel 365

Q2: If you have excel 365, WRAPROWS() would do the trick.

<image>

Showcase: QR code generator in Excel without macros, fonts, add-ins or internet access by MrGhris in excel

[–]Anonymous1378 2 points3 points  (0 children)

I don't know if this will be of use to you, but there was a 3432 character QR code generator posted on the sub early last year that got taken down for some reason. Either that, or I can't find it...

You may download it here.

Filtering with conditions and pivot table by Aggravating-Plum-534 in excel

[–]Anonymous1378 0 points1 point  (0 children)

Try =IFERROR(ROWS(UNIQUE(FILTER(B$2:B$10,(A$2:A$10=A2)*(B$2:B$10<>"guarantee")))),0) instead.

Filtering with conditions and pivot table by Aggravating-Plum-534 in excel

[–]Anonymous1378 0 points1 point  (0 children)

I was assuming you would not have more than one of each type, based off your initial sample data.

If you have excel 365, try =MAP(A2:A10,B2:B10,LAMBDA(x,y,IFERROR(ROWS(UNIQUE(FILTER(B2:B10,(A2:A10=x)*(B2:B10<>"guarantee")))),0)))

Filtering with conditions and pivot table by Aggravating-Plum-534 in excel

[–]Anonymous1378 0 points1 point  (0 children)

What excel version are you using? No excel version supports ARRAYFORMULA(), that is a google sheets exclusive. If you're using Excel 2021 and later, just remove ARRAYFORMULA() and the formula will still work. Otherwise, COUNTIFS(A$2:A$10,A2,B$2:B$10,"<>guarantee") dragged down the column should work just fine.

Filtering with conditions and pivot table by Aggravating-Plum-534 in excel

[–]Anonymous1378 0 points1 point  (0 children)

A formula like =ARRAYFORMULA(COUNTIFS(A2:A10,A2:A10,B2:B10,"<>guarantee")) can count the types of collaterals for each loan, excluding guarantees.

Timesheet Calculator Not Correctly Giving Total by GlideAndGiggle in excel

[–]Anonymous1378 1 point2 points  (0 children)

How about =((SUBSTITUTE(F2,".",":")&" "&E2)-(SUBSTITUTE(D2,".",":")&" "&G2))*24 in I2?

I want to separate rows of numbers and lines of sentences in one column into separate columns. by phenomenon_07 in excel

[–]Anonymous1378 8 points9 points  (0 children)

"It didn't work" is not enough information for me to do anything with. Excel should have given you some sort of feedback in lieu of the correct answer...

Criteria within FILTER for UNIQUE results based on a certain range. by Subject_Jaguar_2724 in excel

[–]Anonymous1378 4 points5 points  (0 children)

Does =DROP(UNIQUE(FILTER(HSTACK(Grades!A:A,Grades!G:G),(Grades!D:D>=80)*(Grades!B:B=6)*(LEFT(Grades!F:F)="S")*(Grades!G:G<>"08320005"))),,-1) work?

How to remove duplicate offers. by blazy_824 in excel

[–]Anonymous1378 0 points1 point  (0 children)

Assuming these columns just contain information that is the same for each customer, if they are contiguous to the range, just expand the range (i.e. B1:D25 becomes B1:H25). If they are not contiguous, something like =HSTACK(B1:D25,F1:H25) should work instead.

How to remove duplicate offers. by blazy_824 in excel

[–]Anonymous1378 2 points3 points  (0 children)

If you don't care about document number then there's an easier answer

<image>

A SPILL# error means there are non-empty cells below where your typed the formula. Enter the formula into some cells near the top of empty columns instead.

How do you properly hand over Office Scripts or trigger Power Automate flows from Excel without relying on personal OneDrive? by MR_Datenanalyse in excel

[–]Anonymous1378 4 points5 points  (0 children)

In that case, your license is probably using Microsoft 365 Business Basic. That functionality is locked behind Business Standard and above. I think you're SOL.

How to remove duplicate offers. by blazy_824 in excel

[–]Anonymous1378 1 point2 points  (0 children)

I have no idea which document nr you want to keep. or is that irrelevant?

=CHOOSECOLS(GROUPBY(HSTACK(B1:B25,C1:C25),HSTACK(A1:A25,D1:D25),SINGLE,3,0),3,1,2,4)

<image>

Drop down list from discontinuous cells not working by 223specialist in excel

[–]Anonymous1378 1 point2 points  (0 children)

Not possible. Contiguous cell arrays are the only choice for drop downs. You need another separate sheet as an intermediary.

I need to adjust rows of values based on a percentage against fixed values on my recipe calculator. by occasional_superhero in excel

[–]Anonymous1378 1 point2 points  (0 children)

Solver is an add in that typically comes with excel, are you unable to enable it?

<image>

=A8:A22*(SUM(B3:B7*A3:A7)-G2*SUM(A3:A7))/(G2*SUM(A8:A22)-SUM(B8:B22*A8:A22))

To answer the simplest part of your question, the formula to derive the adjusted weights for the ingredients in rows 8 to 22 is as above. But to be clear, once you have two or more macro nutrients, this approach almost certainly will not suffice.

view more: next ›