Tag Info

New answers tagged

0

The fact you want it to be dynamic AND hieractical (apple pie takes precident over apple), makes it a bit tough, but if you're willing to have it statically programmed you could do something like this: Row 1 - Your search text Row 2 - Your result text B1=Apple B2=Apple B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"") C1=Apple Pie ...


0

cut and paste everything into note_pad delete what ever left in xl Select all needed cells, columns row to text format re-paste from note_pad if you need later you can change to date, time formats


0

I would resolve this with the Power Query Add-In. It takes a few steps to get there but no code or changes to your input data structure are required. I've built a prototype which you can view or download - its "Power Query demo - searching for a list of keywords and categorizing" in my One Drive: https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398 ...


0

I think you may consider =SUMIFS() function instead. Your condition can be rewritten as: Condition 1: Column B is filled in and Column C is blank; OR Condition 2: Column B is blank and Column C is filled in; OR Condition 3: Column B and Column C are both filled in. Since the 3 conditions are mutually exclusive, the complicated SUMPRODUCT is essentially ...


0

Aha - I needed the SIGN function: =SUMPRODUCT(A2:A10,SIGN((B2:B10<>"")+(C2:C10<>""))) I still find it weird that, as far as SUMPRODUCT is concerned, TRUE + TRUE = 2. And that 2 doesn't just evaluate to TRUE when -- is in play.


0

I would use the Power Query Add-In for this. You can start a Query from an Excel Table. The Split command can be used to split a column based on a delimiter (e.g. Comma). Then I would use the Unpivot command to turn those multiple columns into multiple rows with a single column.


3

a b c d e f 0 0 0 1 0 0 d =INDEX($A$3:$F$3,1,MATCH(1,A4:F4,0)) 0 0 1 0 0 0 c =INDEX($A$3:$F$3,1,MATCH(1,A5:F5,0)) Trust this helps


0

I too did not find a better solution than the Scott's one. But combined with the approach from here it could be almoste bearable, I think: http://www.ozgrid.com/Excel/named-formulas.htm Let's say I have formula like this = IFERROR( INDEX(INDIRECT("EsZkouska"); ...


0

I'm not in front of a Windows computer with Excel right now, but I believe this code should create a new worksheet, and copy the data into the new sheet, splitting the names into separate rows. Sub CopyRowsAndSplitNames Dim srcSheet As Excel.Worksheet Set srcSheet = Excel.Application.Sheets("Sheet1") ' Change this to whatever your worksheet is called ...


0

Click on the column you want to change the format... Press CTRL + 1 on keyboeard, A dialogue box will popup. Click on Time and change the format


1

Use the formula =IFERROR(DATEVALUE(A1),A1) (where A1 is the beginning cell of your date column) at the top of a new column, fill it down to the end of your table, then copy the new column and use Paste Special -> Values. Format the new column as Date.


0

This may not be very eloquent, but it seems like it will work for your requirements. Highlight the Range to be formatted Click the Conditional Formatting button, then Manage Rules Create the five rules as shown in this image, use the "Formula" option The order of the rules, the values, the comparison operator, and the stop if true flag are all very ...


1

I built a solution to this using the Power Query Add-In. You can download it from my OneDrive: https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398 Here's a description of how I built it: Using Power Query, I created a Query ("Table1") from the Excel table. I unchecked the option to Load to worksheet - this is just for input to the next Query. ...


0

Under Developer -> Visual Basic, put the following in a new module: Sub CopySelection() Dim xlSel As Excel.Range Set xlSel = Excel.Application.Selection xlSel.Copy Excel.Application.Sheets("Sheet2").Range("A1") End Sub Then, under Developer -> Macros, select the sub, click Options, and assign a key.


0

There is more than one answer, but I would copy from the last date cell that is correct, Click the cell and hit ctrl-c. Then use your favorite method to select the destination cells, unless there are 40,000 rows I usually just click the first cell hold the click and drag to the end of the range, then use PASTE SPECIAL and select formats. You could also copy ...


1

Your example doesn't work, but I would expect that in your second row, if you do =SUM($A$1:A1) (or whatever your first cell is), and then drag that formula down besides it, it will do what you want. $A$1 is always cell A1 (the top of the list), and the regular A1 reference will update to always be "the cell to the left", making your sum be "from the first ...


0

I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string. In the code below, you'll obviously have to tweak it a bit for your needs, ...


1

I'm not sure what happened on your example, but this is a pretty straightforward Excel charting task. Here's another look at the process: Enter your data (the format from your sample is fine, it doesn't have to be dates). Create a new Stacked Bar Chart. Add Start Time as your first data series (first chart below) and Program Name as your Horizontal X Axis ...


0

Excel recognized your entry of yyyy-mm-dd, which is why it automatically converted it to your Regional Short Date format (Windows 7: Start > Control Panel > Region and Language > Formats). What you wanted was for Excel to recognize and display the format. In addition to the above, if you want a date that responds to the user's short date format, add an ...


0

"[Alt]+EIJ" -- Use the old short cut keys: Highlight your text area and go [Alt]+E, then I, then J. There also is a "FILL" icon in the home/edit ribbon: "up, down, left right, across worksheets, series and justify".


1

Here goes an idea whithout VBA: "offset". Lets say your original data is in "Sheet1" and you want to mirror every cell at "Sheet2". For any cell on Sheet2 you can use: =OFFSET(Sheet1!$A$1,Row(A1)-1,COLUMN(A1)-1,1,1) Sheet1!$A$1: is you main reference cell. This is the only cell you can´t move or delete. You can leave Column 1 blank and hide it, to ...


1

You could do something like this: =SUMPRODUCT($A$2:$A$11*($B$2:$B$11&$C$2:$C$11<>"")) The idea is that the concatenation of B and C is blank only if both are blank.


-1

a way to answer the question without macro could be XL comparator. Regards Marc


0

This worked for me in Excel 2010. Just select the column, right click, Format cells, Custom and choose the option that says 0 (second option below General).


0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.


0

Sounds like a Lookup problem Assuming Column A is the column with 205 entries and your data has headers: in Cell D2 use a vlookup formula: = VLOOKUP(A2, $B$2:$C$440, 2, FALSE) Copy down this formula to D205 et voila


0

This should work for whitespace. I'm not sure about 'special characters'. Open your CSV file in Excel and click the new sheet button (or hit <Shift>-<F11>. In cell A1 of the new sheet, insert the following formula: = TRIM(Sheet1!A1) Copy/down and copy/across this formula so that it covers the same number of columns and rows as in your CSV ...


0

I hope you need to find out the duplicate records from Column A to Column B. Pivot table is a better option for find a duplicate records Microsoft Excel. Select the whole data(Column A and Column B) select Insert tab -> Pivot Table. Select the column A and Column B in Row format, all the duplicate entries are filtered by itself.


0

One suggestion, maybe you've heard of this function maybe you haven't. It's DATEDIF, it's not "officially" an Excel function anymore but is still supported for backwards compatibility. It can calculate time differences in (d)ays, (m)onths, (y)ears, minutes, or seconds. It can be found on any search engine pretty easily just search for Datedif function. I ...


0

After reviewing the original version of my answer, I realized that consolidation of all necessary formatting formulas was actually a bit easier than I'd expected. That, and I was using the wrong colors. Here's how you can do everything you need, fully within Conditional Formatting. All fields below are manual-entered. Make sure that columns C, E, and F are ...


3

(This is my first answer posting on this site so advice of how to improve my response is welcomed) I don't know how your sheet is set up but the easiest way to get this to work is to setup columns like this: A: Employee B: Departure Time C: Expected Half-Way Time D: Actual Half-Way Time E: Expected Arrival Time F: Actual Arrival Time How you populate those ...


1

I have found the errors in my way. I had to do the following: Format Start Date/End Date as mm/dd/yyyy Format End Date/End Time as HH:MM:SS (24 hr format***) Then I was able to use the formula (in R2) =(P2+Q2) - (N2+O2)


1

I would use the Power Query Add-In for this. You can start a Query from an Excel Table. I would start one Query called Fruit, and Filter on category = fruit. Then I would start another Query called Vege, and Filter on category = vege. Then in the Vege Query I would add a Merge command to join to the Fruit query using the Item columns, keeping rows that ...


1

I don't think you will be able to do this with a pivot table as the filtering is not that advanced on the grouped items. Or at least it isn't that automated. I was able to do this with an SQL query. You can query Excel from Excel using MS Query and a data connection (simply point the datasource to the file you are working on). Here is the final result: ...


-1

To answer this Question, first let's look at the typical pivot table. There's the table generated from the data and there's the pivot table field list. To do what you are asking go to the pivot table field list and where you see category place the cursor over that then you will see a small upside down triangle the right. Click on the triangle. This will ...


1

Assuming you have your data set up like so: Select the data and then select "Format as Table". Right click on the table and then select Table --> Totals Row. You can play around with what types of functions for each column, but sum will give you the total within that column. You can then filter based on entries using the down arrows next to the ...


0

Use a blank helper (evil) column and copy and paste this all the way down. =IF(B3>NOW(),B3,"") I will call it column T. Then in "next up" field enter. **=MIN(T1:T1000) As a function something like this: Function Soonest(scolumn As String) As Date ' ' Dim a, b Dim test(20000) As Date Dim Min As Date b = 0 For a = 1 To 20000 If ...


0

This is happening because Excel sees these cells as numbers, but because they are so long they are being interpreted as Floating Point numbers. Number formatting precision in Excel maxes out at 15 digits as explained here. What you should probably do is handle each value as a string. Adding a single ' at the beginning of every cell with data in it should ...


0

If I understand your question correctly, you would like a formula that will read the first and last name on F1, locate the corresponding row on F2, and return the email address. This array formula will accomplish this. Make sure you press Ctrl+Shift+Enter when entering the formula because it is an array formula. =INDEX('F2'!F:F, MATCH(A1&B1, ...


0

If this field is in the Pivot Table then it is also in the Field List, which means you can uncheck the box beside the Field name in the field list, that should hide it.


0

If your numbers are unique, this will work: Next smallest: =SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)+1) Next largest: =SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)-1) If they're not, then you can do some more complex manipulations using array formulas or helper columns. You'll also need to decide how you want to handle duplicate numbers (return ...


0

Generally: Excel accepts D:D as replacement forD1:D<LARGE NUMBER> - making a reference for the entire column. That is not the case in e.g. LibreOffice Calc (D1:D1048576 appears if you select a column and go for "Condition" in Conditional formatting). For conditional formatting you should end up with a SINGLE CELL reference though. That reference ...


0

Here’s a solution that shows you the times within the “time zones” (bins):         (i.e., the equipment is unavailable from 0900 to 1200, from 1200 to 1600, and from 1600 to 1700).  The formulas in cells D5-D7 are: D5 = =IF(AND($B5>D$2,$B5<D$3), $B5, IF(AND($B5<D$2,$B6>D$2), D$2, "")) D6 = =IF(AND($B6>D$2,$B6<D$3), $B6, ...


0

You say your data is in multiple sheets, so this may not apply, but if all the data points were in a single table/sheet, this would be a very simple task for a pivot chart. If this is your data: Student | Grade Mike | 1 Matt | 3 Macy | 4 Mark | 4 Mary | 1 Mutt | 2 A pivot table would like this Grade | Count of Students 1 | 2 ...


1

Type the data into Excel or LibreOffice in two columns like this: Score Count 4's/Best 3 3's/Above-Average 5 2's/Below-Average 4 1's/Worst 2 Select all of it, click the "Chart" -icon in the icon filled toolbox (at the top of the window). It really shouldn't be more than so -- you will get a dialog for setting options, but ...


0

I've run into this before. This is very similar to the question Excel conditional formatting fragmentation. I am pretty sure you can use the same solution, only enter =$A:$Q as the range in the Applies to field.


2

One workaround is to make your data a dynamic named range. This will allow you to apply the conditional formatting to the named range by name, which will remain constant, while the range the name applies to may change as you add or remove data. How to set up a dynamic named range: Go to the Formula ribbon and click Name Manager. In the Name Manager, click ...


1

The answer from @Raystafarian does not work when selecting the whole table. For some reason Excel doesn't allow the paste with links. However it can be done by copying it twice, first time with only headers and second time with only data. I don't get that.


0

Add an ActiveX textbox (Developer tab, Controls section, 'Insert' button. default name is TextBox1). Add a basic form control button (default name should be Button1) Select button and click "View Code" in the same Controls section on toolbar Then the sub should look something like this Sub Button1_Click() Sheet1.Range("C1").Value = ...



Top 50 recent answers are included