Search results

Does it have to be vba? In cell E1: =SUM(--ISNUMBER(SEARCH("HRG",B3:B12))) This is a case-insensitive search, for a case-sensitive version change SEARCH to FIND Depending on your version of Excel, you may have to commit this formula to the cell with Ctrl+Shift+Enter rather than the usual Enter...

One way: Get your value in the named single cell dataset_actual as follows: I chose to name the step ColmHdr: ColmHdr = Table.FirstValue(Excel.CurrentWorkbook(){[Name="dataset_actual"]}[Content]) Then in the add column step use the likes of this: = Table.AddColumn(Source, "Added Colm", each...

Now that I understand what you want (thanks @AliGW !) there's another formula that you could use which also means you don't need the TEXTSPLIT in column BI of the first sheet. In D10:=BYROW($B10:$B66,LAMBDA(a,IF(ISNUMBER(SEARCH(a,XLOOKUP(D2,'RepHunter Contacts - Member...

In the attached is a very clunky offering. On sheet page 1 there's a button at cell M3 which calls a macro Test, which in turn calls the macro blah with 3 arguments being in order: the cell with the lengthy string the cell where the output starts a range of cell(s) having the same width that...

Why?! =INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),1) will give you the first result. =INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),2) will give you the second result etc.

In the attached, such a regex version, converted to a named lambda FilterTitles; when used has hints for what goes where in the function's arguments. See cells F5 and H5. FYI only, the longhand lambda is in cell M5 See the named lambda function in Name Manager To use it in your own workbook...

try:=FILTER(tb_titles[TITLES],BYROW(tb_titles[TITLES],LAMBDA(a,SUM(IFERROR(SEARCH(tb_exclusions[EXCLUDE],a),0))=0))) I think I could do better, maybe with the new REGEX functions.

Take the ,TRUE out that I highlighted with a red oblong in my msg#11, and if you want to see repeating months then take out the UNIQUE wrapper too.

There is no Delhi in Sept for A123

If there are more columns it will show them.

=TOROW(FILTER(IF(C13:K23=C5,C12:K12,NA()),B13:B23=C3),3) In the case where the same name visits the same city in the same month more than once you may want to wrap the result in UNIQUE: =UNIQUE(TOROW(FILTER(IF(C13:K23=C5,C12:K12,NA()),B13:B23=C3),3),TRUE)

Another: =ISODD(ROWS(UNIQUE($B$3:$B3)))

F2: =DATE(MID([@[Time Out]],7,4),LEFT([@[Time Out]],2),MID([@[Time Out]],4,2))+TIMEVALUE(MID([@[Time Out]],12,5))-DATE(MID([@[Time in]],7,4),LEFT([@[Time in]],2),MID([@[Time in]],4,2))-TIMEVALUE(MID([@[Time...

In the attached (a separate workbook which interrorgates your attached file) you'll find: 1. Cell A1, a named range (called FileNameAndPath) containing the full path and file name of the workbook to be interrogated which you should adjust. 2. A copy of your combinations table. This is used to...

In the attached: Your Table1 with an added row (S No. 4) Your Table2; with this table it's important to retain the trailing space in the first column header Durations . The rest of the step names can be anything and any number of steps. Note that these tables are now proper Excel tables. After...

Cross posted: https://forums.excelguru.ca/threads/excel-vba-reference-worksheets-using-variables-in-a-table.12153/ https://www.mrexcel.com/board/threads/reference-worksheets-using-variables-in-a-table.1273631/...

Your source data doesn't have any columns named like "121 to 150 days past due (Home Currency)", "151 to 180 days past due (Home Currency)", only "Amt 1", "Amt2", "Amt3" etc. 2 Things: If your data did contain those column headers it would work as it is (tested here), with the proviso that...

More convoluted than I thought. See attached.

See conditional formatting in the attached.

One thing to be aware of when concatenating values to use in VLOOKUP or MATCH is a possible incorrect match. The following 2 rows are different, yet the helper column is the same: To help reduce the possibilities of such incorrect matches you can put an unusual character between the 2 values...

Also pivot table. See attached at cell D2.

Small update to the attached workbook (Date columns in the Source table with no data are preserved in the output table).

In the attached workbook there's your source data on the sheet Source and a Power Query query output on sheet Result. The source data needs to be as follows: The first 4 columns must be "Area", "Position", "Code", "Company" but those 4 can be in any order. The rest of the columns are what the...

In the attached, a udf called blah. This formula needs to be array-entered (commit the formula to multiple cells at once using Ctrl+Shift+Enter rather than the usual plain Enter). The area you need to select needs to be 5 cells wide, but the number of rows will vary according to your data; if...