# Search:

Type: Posts; User: shift-del; Keyword(s):

Page 1 of 6 1

1. ## Thread: Rank using 2 tie breaker columns

by shift-del
Replies
4
Views
158

### Re: Rank using 2 tie breaker columns

Hi

Alternatively you can create a helper column:

=C2-D2%%-E2%%%%

... and rank on this helper column.

=RANK.EQ(H2,\$H\$2:\$H\$24)
2. ## Thread: creating multiple rows of data based on values in columns

by shift-del
Replies
4
Views
100

### Re: creating multiple rows of data based on values in columns

Hi

Load the table into Power Query.
Select column "ID".
Right click. "Unpivot other columns".
Close and load the new table into Excel.
3. ## Thread: A lookup with a SUM function?

by shift-del
Replies
1
Views
154

### Re: A lookup with a SUM function?

Hi

Try this:

=SUMIFS(Log!\$B\$1:\$B\$4,Log!\$A\$1:\$A\$4,A20)

by shift-del
Replies
6
Views
341

### Re: Managing a Large Spreadsheet

I don't know how do you get 1 million rows. I count 500 people x 100 events = 50,000 rows.
5. ## Thread: It was twenty years ago today...

by shift-del
Replies
15
Views
3,031

### Re: It was twenty years ago today...

Hi Bill

Not many communities in the net last for 20 years.
Best wishes for the next 20 years.

Just over the line. But so far no email.

by shift-del
Replies
7
Views
401

### Re: reverse match/index

Hi

Either with a formula:

=SUMPRODUCT((\$K\$3:\$K\$6=B\$9)*(\$L\$3:\$L\$6=\$A10)*\$M\$3:\$M\$6)

Or with a pivot table:
Head1 in columns area, Head2 in rows area and Value in values area.
7. ## Thread: Using Indirect to reference a different sheet

by shift-del
Replies
3
Views
133

### Re: Using Indirect to reference a different sheet

Hi

Try this.

...INDIRECT("'" & \$CF\$4 & "'!D717")
8. ## Thread: Help With cleaning data to a specific description

by shift-del
Replies
1
Views
91

### Re: Help With cleaning data to a specific description

Hi

=REPLACE(A1,SEARCH("COLD START",A1)-12,12,"")
9. ## Thread: Merge 2 database file into 1 database file

by shift-del
Replies
5
Views
181

### Re: Merge 2 database file into 1 database file

There are many tutorials on the net:...
10. ## Thread: Merge 2 database file into 1 database file

by shift-del
Replies
5
Views
181

### Re: Merge 2 database file into 1 database file

Hi

I would use Power Quer aka Get and transform.
Can be done easily with the UI.

PS: D:\DepositData.xlsx1 ist not an Excel file.
11. ## Thread: Extract all cells from a column that meet criteria

by shift-del
Replies
14
Views
560

### Re: Extract all cells from a column that meet criteria

If you are familiar with Power Query then you can use this M-Code.

let
Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
#"Changed Type" =...
12. ## Thread: Extract all cells from a column that meet criteria

by shift-del
Replies
14
Views
560

### Re: Extract all cells from a column that meet criteria

No, it is not okay.
The header says =840 but the formula says =930.
13. ## Thread: Extract all cells from a column that meet criteria

by shift-del
Replies
14
Views
560

### Re: Extract all cells from a column that meet criteria

Okay. I also made a mistake.
Let me show you in boolean logic.

<tbody>
Arbeitsblatt mit dem Namen 'Tabelle1'

A
B
14. ## Thread: Extract all cells from a column that meet criteria

by shift-del
Replies
14
Views
560

### Re: Extract all cells from a column that meet criteria

Hi

Example: 1341000930
Is >1600000000 but the last 3 digits are NOT 930.
15. ## Thread: How do I make an INDEX, MATCH, MINIF formula return a value NOT from a blank cell

by shift-del
Replies
4
Views
269

### Re: How do I make an INDEX, MATCH, MINIF formula return a value NOT from a blank cell

As you can see in my solution there is no need for an IF() since MIN() already ignores blank cells and text values.
16. ## Thread: Help with finding unique word in column from a list

by shift-del
Replies
4
Views
172

### Re: Help with finding unique word in column from a list

It works fine in my sheet.
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Calibri; color:#000000; border-collapse:collapse;...
17. ## Thread: How do I make an INDEX, MATCH, MINIF formula return a value NOT from a blank cell

by shift-del
Replies
4
Views
269

### Re: How do I make an INDEX, MATCH, MINIF formula return a value NOT from a blank cell

Hi

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"></colgroup><tbody>
Arbeitsblatt mit dem Namen 'Tabelle1'

A
B
18. ## Thread: Help with finding unique word in column from a list

by shift-del
Replies
4
Views
172

### Re: Help with finding unique word in column from a list

Hi

=LOOKUP(9^99,SEARCH(Sheet2!\$A\$2:\$A\$5,C2),Sheet2!\$A\$2:\$A\$5)
=VLOOKUP(F2,Sheet2!\$A\$2:\$B\$5,2,FALSE)
19. ## Thread: Is this possible in Excel??

by shift-del
Replies
8
Views
336

### Re: Is this possible in Excel??

Well, I could imagine at least two more solutions: DAX measure in the pivot table and the new dynamic array function FILTER() - currently only available on the Insider Channel.
20. ## Thread: Vlookup with singular or multiple lookups

by shift-del
Replies
2
Views
161

### Re: Vlookup with singular or multiple lookups

Hi

=LOOKUP(9^99,SEARCH(B11,\$F\$11:\$F\$12),\$G\$11:\$G\$12)
21. ## Thread: Finding Best and Send Best Value and ignore #N/A

by shift-del
Replies
4
Views
320

### Re: Finding Best and Send Best Value and ignore #N/A

Hi

<colgroup><col style="width: 28ptpx"><col width="34,5pt"><col width="48,75pt"><col width="46,5pt"><col width="46,5pt"><col width="47,25pt"><col width="45,75pt"></colgroup><tbody>...
22. ## Thread: Is this possible in Excel??

by shift-del
Replies
8
Views
336

### Re: Is this possible in Excel??

Hi

Pivot solution:
Hole ID and Au in rows area.
Au and Depth from in values area.
Change function for Au to Max.
Select a cell in the Au column in the pivot table.
Right click -> Top 10 ......
23. ## Thread: SUMIFs - simple example

by shift-del
Replies
10
Views
733

### Re: SUMIFs - simple example

Hi

=SUM(SUMIFS(B2:B4,A2:A4,{"A";"B"}))
24. ## Thread: Problem with ISBLANK when there is a formula cell

by shift-del
Replies
2
Views
195

### Re: Problem with ISBLANK when there is a formula cell

With some helper columns.

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
Arbeitsblatt mit dem Namen 'Tabelle1'

...
25. ## Thread: Calculation based on what's in a cell

by shift-del
Replies
6
Views
284

### Re: Calculation based on what's in a cell

There is a specific function for unit conversion.
But you have use the proper units. In this case l, ml and oz.

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col...
Results 1 to 25 of 150
Page 1 of 6 1