Posts by Yard

  • Find Common Numbers Between 2 Columns

    Re: Find Common Numbers Between 2 Columns


    Wow, who did you upset to get this job? ;)


    There will be a VBA solution, and I'm sure there'll be other threads on here to give you some ideas, BUT see attached for a not-very-elegant solution which might suit if this is a one-off or infrequent task.


    Use Data > Text to Columns to split up the data.
    Do a COUNTIF per each separated item to check for duplicates.
    Concatenate all the resulting values.


    Feels like there's a better way, but can't think of it right now.


    Hmmmmmm....

  • Display Selective Info From Sheet

    Re: Display Selective Info From Sheet


    P/T is not THE answer, it's MY answer.


    The row and column fields ina P/T are perfect for text values.


    You could drag one of them into the Values area as well, but just ignore it (makes it look tidier than no values).


    As for automation, have a look at dynamic named ranges for your source data and plenty ofthreads on here about auto refrshing P/Ts.


    HTH


    PS. Can't open your attachment right now.

  • Count Formula Cells

    Re: Count Formula Cells


    Am stuck on mobile internet with no Excel, but isn't there a way of doing this with =GET.CELL(48,myrange) in a named range? Might need the use of a helper row/col though.

  • Auto-Extending Graph/Chart Range

    Re: Auto-Extending Graph/Chart Range


    Cant open yourattachment right now, but a simple way would be to add a helper column to your data which indicates where the change of date is/is not present. Then your dynamic range can use that as the row offset value.

  • Count Specific Records By Month

    Re: Finding The Number Of "n/a" Records In Each Month


    Hi,


    First solution is a PivotTable. This will allow you to group by month and then filter by NA if you want.


    Post back if you try it and need more detail.


    HTH

  • Calculate On & Off Time

    Re: Find On Off Time And Report On One Sheet


    Quote from Yard

    For me it would be a big help to see a SAMPLE of your data


    Rich, the sheet with the data is protected, and it isn't clear to me what you want -there are 4 sheets on there with all sorts of data.


    Unless someone else can figure it out, I just want a simple sample with a few records, and a view of what you want to see.

  • Count Used Cells In Range

    Re: Return Row Count


    That's a better thread title - although it still doesn't describe what you want!


    To return a row count you can use:


    =ROWS(A1:A10)


    However, you want to return the rows containing data.


    Suggest you set up a Dynamic Named Range for the area, but depending on how many columns you want to test it may be useful to have a helper column, and you can count that.


    So, which columns contain the data you want to look for?

  • Calculate On & Off Time

    Re: Find On Off Time And Report On One Sheet


    Welcome Rich,


    For me it would be a big help to see a SAMPLE of your data and the results you would like to see - can you post a sample?


    Sounds like a job for a Pivot Table, but let's see first.

  • Set-up Spreadsheet For lookups & Reporting

    Re: Looking Up Values


    Totally agree with AAE - otherwise you'll give yourself a headache, not to mention an unwieldy and unnecessarily large workbook!


    Also, FWIW, as I note your experience level, make sure you use the correct data type for your database fields, e.g. make sure numbers are numbers, dates are dates (rather than typed-in text). I see loads of examples of people tying themselves in knots....


    If you have further specific questions, feel free to ask them (following the rules on thread titles of course!) no matter how basic they may seem.




    PS Welcome to Ozgrid, and don't forget......."i" before "e", except after "c"......... ;-))

  • Chart Last X Cell Values

    Re: Chart Last X Cell Values


    Dave, I think the OP wants this to always give the last 5 entries in an expanding/dynamic list:


    =OFFSET($A$1,COUNTA($A$1:$A$1000)-5,0,5,1)


    aandrax, you need to change the $A$1000 to something meaningful for your data set (i.e. bigger)

  • Lookup, Match & Combine Data

    Re: Lookup, Match & Combine Data


    Best I can suggest is to create a Pivot Table from the Company Information data. This will at least list all relevant abbreviations per customer.


    Otherwise you may be looking for some code to cycle through your data etc..


    FWIW I would ask myself WHY I want the resulting data to look like that - what's the real end result? The effort can sometimes outweigh the outcome.


    edit: couldn't rest till I could think of a formula solution. The attached goes some way to what you want. HTH.

  • Lookup Not Finding Cell Number

    Re: Cell Referencing With Lookup Formula


    I bet either F10 is formatted as text, or the entries in Detailed List column B are formatted as text. Either way, convert them to values or use the VALUE function in your lookup.


    edit: DOH! just re-read it. You have F10 in apostrophes. Just use


    =VLOOKUP(F10,'Detailed List'!$B$7:$Q$1500,5,TRUE)