Search results

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...

Select a blank cell, copy it, select the cells with text you want to convert to numbers, right-click, paste special, choose Add, OK.

Try the likes of =AVERAGE(VALUE(K2:K5))

In the attached, pivot chart at cell M22 of pivot table at cell B22. In the background is a Power Query query which (a) unpivots your source data, (b) outputs to a pivot table! Options in the pivot table allow hiding/showing items with no data. Pivot tables need refreshing after the source data...

What's wrong with copy/pasting?:

Sparklines?: maybe: Atached.

Two ways: 1. Paste the data into Table1 (cell B7 of the BUILD INFO sheet) overwriting what's already there and ensuring the new data is completely covered by the table extents and that all the old data is gone. Resizing of the table mostly happens automatically, but if not, you can adjust the...

So you think that 13:58:40 with 1 min 20 secs before 14:00 is closer to 14:00 than 13:58:57 with 1 min 03 secs before 14:00? I'm sure there's a world somewhere where you're right.

Raw data: Which is the closest before 14:00?: I say: You say:

In the attached: On sheet SCHEDULE, a table at cell M7 which you can adjust. It contains the start times of breaks and handover times in the first column and the duration of such events in the next column. A handover has 0 duration. On sheet BUILD INFO: A table at cell B6 which is your raw data...

Not tested, change .Range to .Databodyrange

See attached, result table at cell N2. No attempt made to make it 'elegant'!

Various solutions from other responders in columns B:E. This should translate to Italian formulae when you open it.

Recognises it here: after clearing the cell causing the SPILL error:

Did it work?

I'd guess, unlike the dataset you gave, that there are some Project IDs without a dot at all, truly top level ones. To debug, you can enter parts of the equation into a cell, eg. =TEXTBEFORE($A$2:$A$982,".")=TEXTBEFORE($A2,".") to get the full column of results. You can change the $A2 to another...

Test: in a cell in row 2: =IF(MIN(IF(TEXTBEFORE($A$2:$A$31,".")=TEXTBEFORE($A2,"."),$B$2:$B$31))=$B2,"top level", "") copy down.

The thread is fine here. I just wonder how many people will respond, because in that workbook's Module1 there are some 1300 lines of vba, 720 of which are actual code. I don't know who will be prepared to sift through it to get you want you want. There have been 8 views of that workbook so far…