Chandoo.org - Learn Excel & Charting Online - Forums Tag: column

Chandoo.org - Learn Excel & Charting Online - Forums Tag: column - Recent Posts http://chandoo.org/forums/tags/column Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Wed, 22 May 2013 06:59:24 +0000 http://bbpress.org/?v=1.0.1 <![CDATA[Search]]> q http://chandoo.org/forums/search.php Debraj Roy on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-81263 Thu, 28 Feb 2013 20:08:17 +0000 Debraj Roy 81263@http://chandoo.org/forums/ Hi Skidragon, Thanks for sharing the MACRO... :) By the way.. * Select all Cells.. and Change Protection "Unlocked" * Select Only A:R Column .. Change Protection to "Locked" * Protect Worksheet... Password is Optional... * Provide all Permission.. Except Format Column.. (uncheck) Will also "Lock the width of specific columns".. :) but suggesting you to ignore it.. Regards, =DEC2HEX(3563) skidragon02 on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-81253 Thu, 28 Feb 2013 18:36:36 +0000 skidragon02 81253@http://chandoo.org/forums/ @sgmpatnaik - Here it is: <pre><code>Sub Column_Widths() Dim ws As Worksheet Application.ScreenUpdating = False For i = 3 To 29 With Sheets(i) .Columns("A:A").ColumnWidth = 5.57 .Columns("B:B").ColumnWidth = 11.71 .Columns("C:C").ColumnWidth = 8.86 .Columns("D:D").ColumnWidth = 8.86 .Columns("E:E").ColumnWidth = 9.43 .Columns("F:F").ColumnWidth = 2.86 .Columns("G:G").ColumnWidth = 17# .Columns("H:H").ColumnWidth = 7.29 .Columns("I:I").ColumnWidth = 32# .Columns("J:J").ColumnWidth = 32# .Columns("K:K").ColumnWidth = 16.29 .Columns("L:L").ColumnWidth = 7.71 .Columns("M:M").ColumnWidth = 10.29 .Columns("N:N").ColumnWidth = 4.86 .Columns("O:O").ColumnWidth = 7.14 .Columns("P:P").ColumnWidth = 4.86 .Columns("Q:Q").ColumnWidth = 6.29 .Columns("R:R").ColumnWidth = 5.57 .Rows("3:103").EntireRow.AutoFit End With Next Application.ScreenUpdating = True End Sub</code></pre> sgmpatnaik on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-78079 Tue, 12 Feb 2013 03:40:49 +0000 sgmpatnaik 78079@http://chandoo.org/forums/ @skidragon Hi if your code works fine then it is better to upload the code here because it is helpful to some one if he has same problem or required Thanks SP skidragon02 on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-78049 Mon, 11 Feb 2013 23:44:22 +0000 skidragon02 78049@http://chandoo.org/forums/ NVM, I wrote a Macro to take care of it. skidragon02 on "Way to Lock Column Widths?" http://chandoo.org/forums/topic/way-to-lock-column-widths#post-78043 Mon, 11 Feb 2013 22:44:08 +0000 skidragon02 78043@http://chandoo.org/forums/ Is there a way to "Lock" the width of specific columns? Luke M on "TAKING SUM OF SELECTED COLUMN RANGE SIMULTANIOUSLY" http://chandoo.org/forums/topic/taking-sum-of-selected-column-range-simultaniously#post-40924 Mon, 10 Sep 2012 13:21:32 +0000 Luke M 40924@http://chandoo.org/forums/ Thanks Narayan. Using that logic, formula would be: =SUM(OFFSET($A$2,3*INT((ROW(A1)-1)/3),0,3,1)) where A2 is the cell with first number, and all the 3's in the formula represent how many cells you want to sum at a time. Copy down formula as desired/needed. NARAYANK991 on "TAKING SUM OF SELECTED COLUMN RANGE SIMULTANIOUSLY" http://chandoo.org/forums/topic/taking-sum-of-selected-column-range-simultaniously#post-40921 Mon, 10 Sep 2012 13:06:52 +0000 NARAYANK991 40921@http://chandoo.org/forums/ Hi Luke , From what I have understood , what Khan means is that he'd like to use a formula to sum the first three values i.e. 1 , 4 and 6 to get 11 ; the same formula copied downwards , should then sum the next 3 values i.e. 7 , 7 and 8 to get 22. Narayan Luke M on "TAKING SUM OF SELECTED COLUMN RANGE SIMULTANIOUSLY" http://chandoo.org/forums/topic/taking-sum-of-selected-column-range-simultaniously#post-40919 Mon, 10 Sep 2012 13:03:40 +0000 Luke M 40919@http://chandoo.org/forums/ Welcome to the forums! Can you elaborate on what you mean by "simultaneously"? In Excel, all formulas are usually calculated at the same time, so I'm not sure what you mean. Perhaps you could add on to your example and show what you would like to see? samkhan79 on "TAKING SUM OF SELECTED COLUMN RANGE SIMULTANIOUSLY" http://chandoo.org/forums/topic/taking-sum-of-selected-column-range-simultaniously#post-40892 Mon, 10 Sep 2012 11:45:29 +0000 samkhan79 40892@http://chandoo.org/forums/ I would to sum selected range of column simoultaniosuy, lets suppose in below data i would like to take sum of column "data" from a till c , and then again a till c simoultaniosuy . Can anyone please tell me some suitable formula i can use. id data a 1 b 4 c 6 a 7 b 7 c 8 xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-34383 Fri, 27 Jul 2012 12:35:27 +0000 xander 34383@http://chandoo.org/forums/ Luke, It works now. Made a mistake in copying the last part of the formula... I changed the row with columns in the last part of the formula and the output is like this now: Column AB Column AC Column AD 120 200 15 130 210 200 15 210 15 So allmost as I want it; but column AB also shows values 200, 210 and 15 and column AC also shows the value 15. Is there a possibility that these "duplicate" values in columns AB and AC are not printed? Thanks, Xander Luke M on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33761 Tue, 24 Jul 2012 12:21:23 +0000 Luke M 33761@http://chandoo.org/forums/ For a single formula to work, we'll need to know either the # of rows your going to use, or the # of columns. It doesn't matter where you first put the formula. My intent was for you to put it somewhere, then copy over to make 3 columns, and then down as needed. Note that I also added some absolute references in the first part of formula so that they don't change when you copy the cell to the right. xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33749 Tue, 24 Jul 2012 09:44:50 +0000 xander 33749@http://chandoo.org/forums/ Luke, It is not working as described above. In which column is the output supposed to be printed? Is that A1? With your suggestion the output is still only printed in column AB and the output is different than it was with the original formula as shown below =IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1)))) In addition I do not know how many columns the data will be spread over, because this can vary. Any idea? Luke M on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33649 Mon, 23 Jul 2012 20:10:09 +0000 Luke M 33649@http://chandoo.org/forums/ If you know how many columns wide you want to spread it over, yes. Assuming you go with 3 columns, change the end of the formula like so: =IF(COUNTIFS($K:$K,"<>"&$A$8,$R:$R,$A$9)<ROWS($AB$3:AB3),"",INDEX($L:$L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),COLUMN(A1)+(ROW(A1)-1)*3))) If it will be more/less than 3 columns, change the "3" at the end of the bolded section. xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33648 Mon, 23 Jul 2012 19:56:40 +0000 xander 33648@http://chandoo.org/forums/ Luke, Sorry for not phrasing this more clearly. Here's an example that will hopefully clarify what I mean (refer to the example in one of our previous conversations). Right now the output in column AB is like this (only values <> 10 are printed): <pre><code>Column K Column L Column AB 10 0 120 10 0 130 10 0 200 12 120 210 15 130 15 10 0 10 0 24 200 26 210 10 0 17 15</code></pre> So all values matching the criteria are printed below each other in column AB. What I want is the output to be like this: <pre><code>Column AB Column AC Column AD 120 200 15 130 210</code></pre> Hope this clarifies what I mean. Thanks, Xander Luke M on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33622 Mon, 23 Jul 2012 16:58:46 +0000 Luke M 33622@http://chandoo.org/forums/ I'm afraid you lost me there xander. What exactly is the question? Could you show an example maybe? xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33462 Sun, 22 Jul 2012 19:39:55 +0000 xander 33462@http://chandoo.org/forums/ Luke, It's really amazing to see how this works!! Now to make it even more sophisticated is it possible to do the following: Column K contain values either equal to 10 or >10, so with the formula above each set of data matching the criteria is printed below each other in column AB. Is it possible to print each dataset to a different column, instead of printing them all to column AB? This would improve the visibility of my spreadsheet. Thanks for your excellent help sofar! Regards, Xander Luke M on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33220 Fri, 20 Jul 2012 17:43:54 +0000 Luke M 33220@http://chandoo.org/forums/ the COUNTIFS function is ok, but the AND won't work as that function returns a single output, and we need an array. We need tu multiply the logic arrays against each other if we want an "and" type logic check (add if it had been an "or"), so make it look like this: =IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1)))) The multiplication is similar to how SUMPRODUCT can be used as a sort of SUMIFS function. <a href="http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/" rel="nofollow">http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/</a> xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33188 Fri, 20 Jul 2012 15:21:54 +0000 xander 33188@http://chandoo.org/forums/ Luke, Many thanks! It works now and your answer has been of great help. However in case I want to expand the selection criteria by adding another IF statement I seem to get into trouble. This is how I set up the formula: =IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(AND($K$3:$K$40802<>$A$8,$R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1)))) So I added criterion $A$9 that is to be found in column R. With respect to the previous formula I changed countif to countifs and added the AND function (both <> $A$8 and =$A$9 need to be met). Can you indicate what goes wrong? Thanks again, Xander Luke M on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33123 Fri, 20 Jul 2012 12:44:36 +0000 Luke M 33123@http://chandoo.org/forums/ It's exactly as you said. To get everything that doesn't match criteria (vegetables) =IF(COUNTIF(A$2:A$10,"<>"&$D$2) < ROWS($E$2:E2), "", INDEX(B:B, SMALL( IF($A$2:$A$10 <>$D$2, ROW( $A$2:$A$10)), ROW(A1)))) One tricky thing that can happen is that if you don't limit the range to look at (A2:A10) to just cells with data, you may pull a bunch of blank cells, since blank <> criteria. xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33111 Fri, 20 Jul 2012 11:37:09 +0000 xander 33111@http://chandoo.org/forums/ Dear Hui, Thanks for your quick reply. I tried the techniques described by Luke M and it actually works if the criteria is set to a fixed number or a text string (in the example it was "Vetgetable" But I would like to filter only those values that are not equal to the criterion. In the example from Luke that would be "<> Vetgetables". Is there a way to do this? Many thanks, Xander Hui on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33072 Fri, 20 Jul 2012 08:51:56 +0000 Hui 33072@http://chandoo.org/forums/ Xander Firstly, Welcome to the Chandoo.org forums. You may want to look at using either an Advanced Filter Refer: <a href="http://chandoo.org/forums/topic/chandoo-advanced-filter" rel="nofollow">http://chandoo.org/forums/topic/chandoo-advanced-filter</a> which has a number of links to Advanced Filter posts here and elsewhere or Look at using a technique that Luke M described at Formula Forensics 003: <a href="http://chandoo.org/wp/2011/11/18/formula-forensics-003/" rel="nofollow">http://chandoo.org/wp/2011/11/18/formula-forensics-003/</a> xander on "Extract numbers from a column that match a criterion" http://chandoo.org/forums/topic/extract-numbers-from-a-column-that-match-a-criterion#post-33063 Fri, 20 Jul 2012 08:35:50 +0000 xander 33063@http://chandoo.org/forums/ Hi I am wondering if you can help me with this: I have file with several columns. Column B contains cells that equals a value of 10 or has values > 10. Column C contains data that is corresponding to the value in column B. Now I would like to retrieve only those numbers from column C corresponding to the data in column B with value > 10. When the values from column C have been retrieved I want to put them in a new column so I have all relevant data in a single column. I looked at several suggestions with the MATCH and INDEX function, but sofar this only works when you want to lookup a specified number. There must be some clever way how this filter should also work for values > 10. Can anyone help me with it? Thanks in advance, xander SirJB7 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22405 Tue, 03 Apr 2012 19:11:13 +0000 SirJB7 22405@http://chandoo.org/forums/ Hi, NJ786! oldchippy correction is absolutely right. I didn't notice that because I tried with recently hours in format "dd/mm/yyyy hh:mm:ss" with the NOW() function instead of just hour values, so I never went before 31/12/1899 (or 00/01/1900). Sorry. Regards! @oldchippy Hi! Thanks for the update. Regards! oldchippy on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22403 Tue, 03 Apr 2012 18:13:41 +0000 oldchippy 22403@http://chandoo.org/forums/ Thanks for the feedback, glad we helped NJ786 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22400 Tue, 03 Apr 2012 18:03:33 +0000 NJ786 22400@http://chandoo.org/forums/ wow! now i have so many options... thanks every one, i thing that should do the job... appreciated once more NJ oldchippy on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22398 Tue, 03 Apr 2012 17:41:48 +0000 oldchippy 22398@http://chandoo.org/forums/ Hi, Away round that one is =A2+1-4/24 NJ786 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22392 Tue, 03 Apr 2012 17:11:21 +0000 NJ786 22392@http://chandoo.org/forums/ 24 hour clock issue see what happens when i try to use the formula... =A1-4/24 where the day goes back to the previous date before 00:00 am, we get a negative value or too large value???? 2:00 ####### 3:00 ####### 3:25 ####### 20:15 16:15 22:45 18:45 23:00 19:00 NJ SirJB7 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22375 Tue, 03 Apr 2012 15:35:46 +0000 SirJB7 22375@http://chandoo.org/forums/ Hi, NJ786! I was wondering why not something like: =A2-XXX/24 where XXX is the integer number of hours to be subtracted or added. Simpler, isn't it? Regards! NJ786 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22350 Tue, 03 Apr 2012 10:12:26 +0000 NJ786 22350@http://chandoo.org/forums/ Ooops my bad ;) NJ786 on "Add or Subtract Time" http://chandoo.org/forums/topic/add-or-subtract-time#post-22349 Tue, 03 Apr 2012 10:11:45 +0000 NJ786 22349@http://chandoo.org/forums/ thanks buddy :)