p._phidot_
  • Member for 8 years, 1 month
  • Last seen this week
  • 夢の世界
2 answers
0 votes
49 views
Is it possible to search specific term in mutiple excel files and count the number of records easily?
3 votes

Assume u have 10 files D:\file1.xlsx to D:\file10.xlsx, and the column interested is A:A. Open file1.xlsx. create a new file, do:   in cell A1 put `=COUNTIF(INDIRECT("[file"&C1&&...

View answer
1 answers
1 votes
102 views
Average Time In Excel
3 votes

Using these data only : Times start in column D6, Average is in D5 (+Average(D6:D370) Times will never be sooner than 23:00 or later than 01:30. You may do : shift all data to (60 minutes) ...

View answer
3 answers
8 votes
82k views
Should I disable "Delayed Launcher" in my startup programs?
3 votes

"Should I disable.." Just disable it. Since you wont be asking if you are not poking around with the startup options.. "What could be the consequences if I disable this program?" Just FYI, in ...

View answer
2 answers
0 votes
92 views
How to convert R1C1 string into A1 string inside excel using other formulas (no VB)?
Accepted answer
2 votes

Improved answer : =ADDRESS(ROW(),$W$1+1,1,1,B$1) This is how you may "convert R1C1 string into A1 string format". ( : Previous answer (for record) : Assuming you are only working with A-Z ...

View answer
3 answers
1 votes
3k views
Convert one column into a new column every 10 rows in Excel
2 votes

Assuming the 324 is located in A1, put this in B1 : =OFFSET($A$1,(COLUMN()-1)*10+ROW()-1,0) and drag it downward & right-wards. idea : use the destination row and column to feed the offset from ...

View answer
2 answers
0 votes
179 views
How to merge Excel worksheets by stacking columns, using an ID column?
2 votes

TLDR : filter unique ID, then use index match + iferror() . Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then : Copy both ID column and ...

View answer
1 answers
0 votes
76 views
Keeping a fixed constant (like letter "M") and a variable (like a number "100") in the same cell in excel but I only need to type the variable?
Accepted answer
2 votes

select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M] done. If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the ...

View answer
1 answers
0 votes
76 views
Excel Autofill with defined steps
2 votes

Since 1,4,7.. and 3,6,9.. is on its own a repeatable numbers.. my (favorite) solution is to use 2 extra column to generate the series.. and one to combine them. using Column C & D as extra column,...

View answer
2 answers
1 votes
103 views
Microsoft Excel. Shelf life: calculate on which day % left equals 75
1 votes

Let : a = expiry date b = some date c = manufacturing date Then : (a - b)/(a - c)= 75% = 0.75 Rearrange : (a - b) = 0.75 * (a - c) - b = (0.75*(a - c)) - a b = a - (0.75*(a - c)) So your ...

View answer
1 answers
1 votes
183 views
Alternative for a filter formula
1 votes

Since the formula works, adjusting the search range should help to reduce the formula load. Try : =FILTER(tab1!L1:L6000,tab1!K1:K6000="US") Please share if it works/not.

View answer
2 answers
1 votes
83 views
Apply title case ("proper case") to an entire spreadsheet?
1 votes

Not really an excel solution, but fast. [ Select All(in excel) , copy > paste in Notepad++ | Select all(in notepad++) > Edit >Convert Case to > Proper Case | Select All(in notepad++) , ...

View answer
3 answers
0 votes
102 views
Combine Excel cells into 1 cell based on date in next column
1 votes

all entries into a single cell on 1 row for each date Assuming the cells of your shared table is A1:B5 In C1 put : =A1 In C2 put : =IF(B2=B1,C1&"|"&A2,A2) and drag downwards. In D1 ...

View answer
3 answers
2 votes
6k views
Is there another option to Picasa with similar ease of use and simplicity?
1 votes

frankly, I just ask my self the same question, I have the same dilemma too. because Picasa doesn't animate the GIF file I opened, and have trouble viewing TIFF (that have multiple images in one file, ...

View answer
1 answers
0 votes
220 views
I want to subtotal/running total in Excel
Accepted answer
1 votes

In cell A6, put =A1 then drag till D6. In cell A7, put =A2 the drag till A9. In B7, put =sum($B2:B2) then drag till D9. Done. I think that's it. Idea : $ is used to 'lock' the row/column, the ...

View answer
2 answers
0 votes
41 views
Excel - Extracting values on certain criteria
1 votes

Put =IF(B1="Vegies",A1,"") in C1, then drag downwards. Please if it works/not. note : have a look on how the if() function works in the documentation/help. just ask if you didn't get any part of it. ...

View answer
1 answers
0 votes
71 views
Excel - capture multiple data columns into 1 cell as text with heading & value
Accepted answer
1 votes

In G2, put : ="- "&$B$1&" : "&B2&CHAR(10)&"- "&$C$1&" : "&C2&CHAR(10)&"- "&$D$1&" : "&D2&CHAR(10)&"- "&$E$1&" : "&E2&CHAR(...

View answer
1 answers
0 votes
30 views
How to separate text in a single row that is in a heirarchy into separate cells
1 votes

TLDR : use another tool to help creating the 'tab' needed. Long version : Download notepad++ paste the text there Find&replace > Replace " " (double space) with "\t" (tab character) salect all, ...

View answer
2 answers
-2 votes
59 views
Excel Line Tools Line
Accepted answer
1 votes

owh.. that is your goal..(the prinscreen share upon request in comment). Now I get it.. (thanks for the print screen).. I had tried what you suggest.. and it happens as it did.. Method : The ...

View answer
1 answers
0 votes
290 views
How do I add multiline text in a text box in MS Excel 365?
1 votes

The button is Alt + Enter. Hope it helps.

View answer
2 answers
1 votes
36 views
How to format a fiscal quarter?
1 votes

in A1 put 1/1/2019. In B1 : =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) and drag right-wards. in A2 : ="Q"&CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)&"'"&RIGHT(YEAR(A1),2) and drag right-wards. ...

View answer
1 answers
0 votes
104 views
Deleting rows in Excel based on individual date range
1 votes

referring to : data from a 2-year period after they completed a certain treatment. just set up an extra column to do : if( AND(( >= ), ( <= (+2 years), true, false) .. then filter the column ...

View answer
1 answers
0 votes
23 views
comparing two cells in two different excel sheets and return difference between corresponding third and fourth cell
1 votes

Assuming : to compare two cells in two different excel sheets (is A1 & B1) and if they match extract the difference between their corresponding third and fourth columns (is C1 & D1) . use : =...

View answer
1 answers
0 votes
387 views
Excel format to improper fraction OR integer
1 votes

I use : =IF(INT(C2)=C2,TEXT(C2,"#"),TEXT(C2,"###/###")) and drag downwards. Seems to solve it just fine. Hope it helps. ( :

View answer
2 answers
0 votes
95 views
Excel - when I open new workbook it recalculates currently opened
1 votes

I normally do this : Windows > Run : Excel > load the big workbook. || then I do did again.. for the 2nd (small) workbook. .. || It seems that running different instance of excel for different ...

View answer
2 answers
0 votes
201 views
Excel. Returning values from multiple columns
1 votes

Updated Answer : Riding on @fixer1234 comment.. I concur with the idea.. just need to add some special consideration for duplicated values. Target: listing 10 cities. Idea : get the lowest product ...

View answer
2 answers
0 votes
33 views
Searching for a value in Excel between tabs
1 votes

This is a bit overkill. Concatenating the index match should work. =IFERROR(INDEX(Sheet2!$D:$D,MATCH($B2,Sheet2!$D:$D,0)),IFERROR(INDEX(Sheet2!$E:$E,MATCH($B2,Sheet2!$E:$E,0)),IFERROR(INDEX(Sheet2!$F:...

View answer
2 answers
0 votes
44 views
Excel - Function used within range critera
1 votes

If you don't mind using another column, then in C1 put : =MOD(B1,5) and drag downwards, then use : =SUMIFS(A:A,C:C,"="&0) Hope it helps. ( :

View answer
1 answers
0 votes
167 views
Excel vlookup not working
Accepted answer
1 votes

in F2.. put : =IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"") & drag downwards. Idea : check if match ...

View answer
1 answers
-1 votes
70 views
How can I make columns in an Excel spreadsheet sortable while maintaining row integrity?
Accepted answer
1 votes

Assuming you data located in column A & B , where Row 1 is the label, and Row 2 is the 1st data. Select both column. Data > Filter . Then click on the small triangle @ A1. Done. Hope it helps....

View answer
1 answers
1 votes
166 views
Excel equation with array constant
Accepted answer
1 votes

Let say array1 is A1:A8, array2 is B1:B8, array3 is C1:C8 . put =SUMPRODUCT($A1:$A8,B1:B8) in A10, then just drag it to C10. USe the '$' sign to 'lock' the reference range/cell. Hope that solves... ...

View answer
1
2 3 4 5 6