Search results

A pivot table would work. In the row labels field box, drag column B, then column A underneath. The result will look similar to this: A ABC XYZ B CDE C FGH It will only show uniques as well, e.g. if ABC A was in the data twice, it would only show once in the pivot. You will need to manually...

fitzbg, Define this named formula name = MMULTARRAY2 =TRANSPOSE(--(Items!$B$1:$G$1=Items!$B$1:$G$1)) You may want to make this dynamically expand/contract based on the number of columns used On the compenents tab, enter this in Column D2 and drag down to D12...

You could also use this in the worksheet module Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) code here End Sub This fires with all changes to the pivot table however.

Here is an alternative method [assumes Excel 2007+]: Using pivot table multiple consolidation ranges will help alter the data as desired. First, remove the product description so your data looks like this: Code 1/1/2012 1/8/2012 1/15/2012 BX24272 45 63 72 QC76354 11...

An alternate partial solution: For this to work the tables have to line up i.e. 1111 on Tab1 Tab2 and Tab3 needs to be in the same cell. Apple needs to be in the same cell on each tab etc On Tab1: =IFERROR(LARGE('Tab 2:Tab 3'!B3,1),"") (copy throughout the table} and format the table with...

Hi Tony, Do you have access to Excel 2007 or higher? =SUMPRODUCT(COUNTIFS(INDIRECT(ROW($A$1:$A$31)&"!B4:B13"),"DEANS",INDIRECT(ROW(A$A$1:$A$31)&"!C4:C13"),">0"))

Hi kalpeshpatel, If you want it to apply to the entire workbook, insert the following in the workbook module: Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Cancel = True End Sub if you want it in a specific sheet, insert this into the...

Hi Julie, I won’t go into the details of XML but I will offer one possible usage of it. Say you have an external file in XML format, such as an invoice or a list of data that you want to bring into Excel. You could open the XML file and manually type in the data into Excel; a time consuming...

Hi TonyTonyTony, Modify your existing formula to this (assuming your sheet names are 1,2,3,4...31): =SUMPRODUCT(SUMIF(INDIRECT(ROW($A$1:$A$31)&"!B4:B13"),"DEANS",INDIRECT(row($A$1:$A$31)&"!H4:H13")))

All formulas? The range should increase automatically, assuming the data is put in columns A:C on Sheet2. The dynamic range may have become invalid somehow. Have you renamed any sheets or changed the structure? Do you still have the column headers on sheet 2? If not put those back in.

Hi cmissal, Copy this into A1:A9 and convert to a table A 1 50 49 25 48 13 62 3 Copy this into B2, it should enter all the way to B9 =INDEX(Table1[#All],ROW(Table1[@])-1,1) A PriorRowA 1 A 50 1 49 50 25 49 48 25 13 48 62 13 3 62 I believe that is what you wanted. Kyle

Try this https://docs.google.com/open?id=0BxTFwBrMVo2nNjhmNTU4ZGItZTdhYS00M2E0LTgwOGMtNjFkNGYwYTI3Y2Rk

My apologies, I have been having a very busy week, unable to reply. Thanks SirJB7 for stepping in. xcruc1at3r, If I understand your question, you are asking if the range can be extended to include the whole column? A:A, B:C, C:C? I wouldn't recommend trying. I would use a dynamic named...

Hi justdream, Sorry for the delay. Thanks for the sample pictures, I see what you mean now. This should give you want you want (except for the orange label thing). Almost the same... Option Explicit Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long...

I posted the link earlier without time to explain what I had done. Here is the method that I used so people don't need to download from the link. I used Narayank991's file for the data On sheet 1 - "data from source 1" - A1:C12 Container No. Destination Quantity 1102 MAS...

I posted the link earlier without time to explain what I had done. Here is the method that I used so people don't need to download from the link. I used Narayank991's file for the data On sheet 1 - "data from source 1" - A1:C12 Container No. Destination Quantity 1102 MAS...

I posted the link earlier without time to explain what I had done. Here is the method that I used so people don't need to download from the link. I used Narayank991's file for the data On sheet 1 - "data from source 1" - A1:C12 Container No. Destination Quantity 1102 MAS...

Hi Chandoo, I don't know if it is related or not, but the home page is stuck on data from Jan 30th (Live in Bangkok with 6 comments). The website just won't refresh on this computer. I've cleared the cache, restarted, set the option for webpages to check for new versions every time, tried...

Hi xcruc1at3r and NARAYANK991 Here is another method, one that identifies all possible error combinations https://skydrive.live.com/redir.aspx?cid=9a0721b634391421&resid=9A0721B634391421!143&parid=9A0721B634391421!136

Hi JBrauny, This won't give exactly what you want, but it will split it into 2 columns as desired Paste your data starting in A2 Put this into B2 =IF(LEFT(A2,4)="LINE",TRIM(RIGHT(A2,LEN(A2)-4)),B1) And this into C2 =IF(LEFT(A2,4)="LINE","",A2) which will give this ABC ABC 3030C...

Hi Ifrazier, I'd suggest that rather than the IF formula, use MAX. =MAX(formula ,3)

edit: Have you tried using the "multiple consolidation ranges" method of a pivot table? With your data, put a 1 in cells C33:C296, next to your data in B and the same in G ,next to the data in F. Put a header above the data, Something like Data in B33 and Count in C33. Put the same headers...

Hi emelen, You should just be able to do this, unless I am misreading your request: enter this into C33 and copy down to C296 =COUNTIF($B$33:$B$296,B33))+ COUNTIF($F$8:$F$31,B33) enter the same formula in G8 except change the =B33 to =F8 and drag down to G31

Hi prasaddn, SUMPRODUCT didn't work because it sums the array and returns a single value, whereas the INDEX function passes the whole array to the LARGE function.

Sure. Did you want the value of the data point and the x & y? if so, just change this part With Me.SeriesCollection(Arg1) xVals = .Values Newtitle = .Name & ": " & xVals(Arg2) & " : X-axis value:" & x & " Y-axis value:" & y End With if you just want the x and y values...