Posts by Batman

    Re: Using one formula detect duplicates

    Hi Csilla,

    Welcome to the Ozgrid forum.

    As I read the requirement, the formula should identify the duplicate rows, not the originals as your formula does. And as far as I can see there are only two duplicates to identify, ignoring the first occurrences.

    I would use this formula, filled down all rows:


    Re: Copying and Pasting formulae between similar Excel tables

    Whether or not you can do that in a single copy/paste operation will depend on the structure of your tables. If they are laid out so that you have all data columns, then all formulas, then it would be relatively straightforward. The problem comes when you copy non-contiguous cells, as you cannot paste them into the equivalent non-contiguous cells in ShipTable.

    Re: Copying and Pasting formulae between similar Excel tables


    If I understand you correctly, you would need to copy and paste special / values just the static values in your PackTable, excluding formulas. When you paste them into the ShipTable, the formulas in ShipTable will automatically fill down to use the table consistent with the other formulas in ShipTable. The formatting of ShipTable will be retained for the new item. You would then need to delete the entire item in PackTable.

    Re: Seperate Each Revision

    Hi shahidsamikhan,

    This thread is cross-posted from another forum. In accordance with the rules that you signed up to, please edit your thread to include a link to the thread in the other forum.

    Re: COUNTIFS that exclude multiple entries


    Try this:

    =SUM(--(A1:A24={"SD: Ford","SD: Lindsey"}))

    Confirmed as an array formula with Shift+Ctrl+Enter, instead of just Enter.

    As far as I can see, there is no need to consider the negatives, i.e what it is not equal to, only the positives.

    Alternatively, this seems to work as an ordinary, non-array, formula:

    =SUM(COUNTIF(A1:A24,{"SD: Ford","SD: Lindsey"}))

    Re: VBA, Advanced Filter


    This should work.

    Re: Create a Macro

    Two points:

    Firstly, when you create a thread title, please do so in a way that includes an indication of what your query is trying to achieve. Thread titles are used in the searches of others looking for a solution to similar problems. The title you created on the other forum was much more appropriate.

    Secondly, in what way do the answers provided on the other forum, within minutes of your question being raised, not solve your problem?

    Re: Copy Values from same cell from multiple worksheets

    Hi iamR3ady,

    One way is to arrange your sheets to sum so that the tabs are consecutive, then use a formula such as

    =SUM('Sheet 2:Sheet5'!A1)

    I have inserted a space in the first sheet name so that you will see you need single quotes around the names if either of the sheets named in the formula contains a space.

    Hope this helps.

    Re: If n-th character of a cell is "A" or "B" or "C", then TRUE, else FALSE

    Based on your quote I'm not sure whether you want your new requirements to be merged with the original reply or the updated one. Assuming the latter, try:


    Whether or not you need the new part to be as flexible as the original will depend on how many options there are in your live data.

    Re: VBA, Advanced Filter

    When you say 'the other columns', do you mean that you now want to duplicate the unique Node and Scenario names in columns C & D? Or is there other data that you will be copying to different columns, and you want to fill that down as well? I must admit, I had assumed the latter.

    Re: VBA, Advanced Filter


    Sorry it took me a while to spot the symbol in the clouds! This is the code that does the filling down:

    lngNextRow = WorksheetFunction.Max(wksSummary.Cells(wksSummary.Rows.Count, 3).End(xlUp).Row, wksSummary.Cells(wksSummary.Rows.Count, 4).End(xlUp).Row) + 1
        If (lngNextRow - lngStartRow) > 1 Then
            z.Resize(lngNextRow - lngStartRow, 2).FillDown
        End If

    The first line of code performs two Ctrl & Up Arrow functions, on columns C & D, then uses the worksheet function MAX to find the greater of the two. It is then that number (+1) that is used to determine the next row for the next sheet.

    The second line of code (If...) checks the new next line against a previously saved first line. If the difference is not greater than 1, then there is no need to fill down.

    The third line of code actually fills down. The variable 'z' has previously been defined as a single cell in column A of the row in which you start pasting the values. The 'Resize' property allows you to take that start point and make it refer to a range of cells of a different number of rows and columns, but starting with the original cell at the top left of the new range (first resize number is the new number of rows, second number is the number of columns). In this case the number of rows is calculated as the next row minus the start row. The number of columns is hard coded as 2. The first row of the new range needs to contain what you are going to fill down.

    If you wanted to fill down, say, columns G & H, the code would change to

    z.Offset(0, 6).Resize(lngNextRow - lngStartRow, 2).FillDown

    This does the same thing, except that it first uses Offset to move the start point. The start point moves 0 rows and 6 columns to the right, i.e. from column A to column G of the same row.

    Re: Identify next empty row


    That is explicitly referencing the workbook identified as 'wb', which your code didn't previously do. If that still errors on assigning the worksheet then there is almost certainly a problem with the name of Sheet1, perhaps an unexpected space somewhere in the name.

    Re: Identify next empty row

    It is probably because your 'Quotes' workbook doesn't contain a worksheet called 'Sheet1', which the first code is explicitly looking for. It is looking for a sheet with that name, not the first worksheet in the workbook.

    The second code will find the next free row on whatever sheet is active when you open the workbook, be it the first sheet or any other sheet.

    Re: VBA, Advanced Filter

    This version fills down the file and sheet names, but not the Node and Scenario values, as you are supposed to be listing unique entries. It also corrects the listing of the file and sheet names in the wrong columns.

    Re: VBA, Advanced Filter

    Obviously sabotaged by the Joker or Penguin! Or more likely it's a problem as I couldn't test it before I posted the code. I will have a look at it, this time in conjunction with the workbook.

    However, the Node and Scenario columns are independently filtered and displayed in the Unique Data sheet. Arbitrarily filling down the last item in the shorter of the two lists within a worksheet may be creating an artificial correlation between two items that may not exist as a combination within the original worksheet. Do you really want those gaps filled?

    Re: VBA, Advanced Filter

    I have noticed a slight flaw in the logic of the code, that may result in problems if there was to be no Node column in a worksheet. I have made some changes to correct that, and to fill down the sheet name, although these are untested:

    Re: Find the "Total" rows and format

    There are a few things I don't understand. For instance, if you want all the data (i.e. non-total) rows to be the same colour, why do you have conditional formatting that sets alternate row colours differently?

    Can you not just use an additional conditional format, to be applied first, with the formula

    and apply the formatting that you want?

    Note that if you use VBA to assign formatting to the cells' .Interior and .Font properties, any conditional formatting that exists in the sheet will override whatever formatting you set to those cell properties with VBA.