Macrordinary

Пікірлер

@Gruhayogam90520 23 күн бұрын

Wow exllent one what u teach never in other xl tutorials.Thank u

@marinapeneva8451 Ай бұрын

Great stuff! I have been struggling with filtering out values in my dataset based on a list of values. I can't make FILTER (IS NUMBER(XMATCH))) work when the values in the column I am filtering are a in the format value; value; value etc. i.e it only works if I have a single value in each cell, but when there are multiple values delimited by something it's doesn't work. Do you have any ideas on how make this work?

@macrordinary Ай бұрын

My thought here would be that there are a few steps involved. Consider this example (a1 is a SEQUENCE(10), b1 is in the format 4;6;7) =FILTER(A1#,NOT(ISNA(MATCH(A1#,INT(TEXTSPLIT(B1,";")),0)))) 1. You need to split the cell by the semicolon: TEXTSPLIT(B1,";") 2. You need to convert those results to an integer: INT(TEXTSPLIT(B1,";")) 3. Then match that array of integers against what you're filtering: MATCH(A1#,INT(TEXTSPLIT(B1,";")),0) At this point, you'll have a list of N/As and matches by position. 4. Determine which results are NA and which aren't: ISNA(MATCH(A1#,INT(TEXTSPLIT(B1,";")),0)) This will convert the list to TRUE / FALSE. 5. Invert the list to get the results that you're filtering: NOT(ISNA(MATCH(A1#,INT(TEXTSPLIT(B1,";")),0)))

@marinapeneva8451 Ай бұрын

@@macrordinary Thank you!

@DataVisualisation Ай бұрын

... it would be great if the file with the data could be made available. Thank you

@macrordinary Ай бұрын

Thanks for the suggestion. I've added the source file in the description.

@lightskinkobe Ай бұрын

You’re a gifted teacher

@lightskinkobe Ай бұрын

Keep em coming please

@chandramohan1418 2 ай бұрын

what to do if to return multiple records.i think FILTER function to be used in that case.

@macrordinary 2 ай бұрын

Absolutely. If you need to return multiple records, then you need to use a filter function. However; XLOOKUP can be used to return multiple columns if your return array is more than one column.

@khonesavanhsoudaloth 2 ай бұрын

Your lecture is very content. It will be good if you give a clue step by steps to navigate the way to do slowly how to write formulas by using LAMBDA along with instructions

@warrenanderson412 2 ай бұрын

This is a great video..however I would recommend that you explain it better in your title.😅

@CAKPRashique 2 ай бұрын

Thanks

@phandongexcel 2 ай бұрын

I like this video ❤❤❤❤

@TheTramos00 2 ай бұрын

Good but just one detail: one of the advantages, among many, of using these powerful functions is not having to use auxiliary columns as they were used before with old school formulas. Here you are using F2# as an auxiliary column. It is better to obtain the result with a single formula in F2. =LET(u_brands;UNIQUE(B2:B26), brands;B2:B26, models;C2:C26, HSTACK(u_brands,BYROW(u_brands,LAMBDA(row,TEXTJOIN(", ",TRUE, FILTRAR(models;brands = row))))))

@DataVisualisation 3 ай бұрын

nice workflow and good idea with the "XLOOKUPS" function ... oh by the way, in one of the last videos it was said that we can make suggestions. How about "FILTERXML" for example

@arindamdutta9371 3 ай бұрын

Excellent..... Subscribed

@DataVisualisation 3 ай бұрын

never seen before, nice way

@abdullonajimov1991 3 ай бұрын

@steventrumble7098 4 ай бұрын

looks good

@lorneneilcampbell 10 ай бұрын

This will not work with multiple users, it will only work with C/users/Name of user - how do you make a shared file work with a button to refresh that data, using power query, so that you do not have to re-point data source to each user/c/locally sync'd onedrive sharepoint folder and they can just press a button whether user 1 is updating it one day or user 2 is updating it one day - is there a way to make the data source look at "Who is logged in", use their username for the file path ?

@khalilbadmus144 3 ай бұрын

This is what I need to know!

@rafaelaaguilar2587 2 жыл бұрын

Thank you for this video. I do not think this worked on my power query. I need to give a user access aside from me. Is there a way to do this?

Ең жақсы KZbin

Пікірлер