Type: Posts; User: JugglerJAF; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.02 seconds.

  1. Replies

    Re: Paste as Values

    copy range (bypasses the clipboard)

    Sheet2.Range("A1:A200").Copy Destination:=Sheet1.Range("A1")

    if only values are needed...

    Sheet1.Range("A1:A200").Value2 = Sheet2.Range("A1:A200").Value2...
  2. Re: Mailmerge data layout - vertical or horizontal

    Thanks for the suggestion. I do already have a macro that converts the data, but it's not as elegant as your solution.

    I'll have a look and see if I can use this shorter approach in the future.
  3. Mailmerge data layout - vertical or horizontal

    (Apologies of this needs to be in a Word forum, but it is 50% Excel related!)

    I have some data that I use to create a mailmerge, and in its raw form, the data looks like this:

  4. Replies

    Re: Extract a number from a long Cell

    A quick and dirty way of doing this (assuminng all your data has exactly the same layout/structure in each cell) would be to use text to columns.

    Specify semicolon AND + as the delimiters. Skip...
  5. Re: how to count and sum cells by color in excel

    This is a question that gets asked a lot, and while it is possible to do (using an add-in like ASAP Utilities, or custom VBA code), it's generally not a good idea to do so.

    Presumably, each colour...
  6. Re: if sheet name is taken, add number at the end

    I've very quickly knocked this up for adding a sheet called "data" with an incrementing number. You should be able to replicate and edit this to add additional "summary" sheets as well.

  7. Re: Macro to search for a number and change it "red"

    I've just tested this and it seems to do what you need.

    As always, please test on a copy of your data file before using on live data just in case of unexpected results!

    Sub set_to_red()...
  8. Calculate smallest number of adjustments to get columns in correct order

    This is possibly more of a maths question than an Excel question, but I thought it was the sort of thing that someone on here may be able to answer...

    Say that I have a data extract from one...
  9. Re: Filter for rows containing "black diamond" question mark

    I've managed to implement a workaround for now by creating a custom function that returns TRUE if the cell contains specified ascii code characters (basically 32 to 126, but excluding 63 which is ?,...
  10. Filter for rows containing "black diamond" question mark

    I have a data file containing data from several different countries, many of which use accented characters in their alphabets.

    The file has been unable to interpret many of these and replaced them...
  11. Re: Add yearly total to chart split by quarters

    Thanks for the info. I don't think I'd ever have worked that out for myself!

    I've got this to work with some sample data, now to try it for real.

    I've got a lot of Excel knowledge, but this...
  12. Add yearly total to chart split by quarters

    I've just discovered Power BI for desktop (literally two days ago) and am enjoying playing around with it, but I've come across a charting problem - something that I could easily do in Excel that I...
  13. Re: Changing part of formula/leaving other parts intact

    You need to make the lookup area completely absolute by putting $ in front of both the column letter and row number like this:

  14. Replies

    Re: Summarising data

    Copy the data from sheet1 to sheet2, filter to show values of zero, then delete those rows (delete entire row, don't just clear the cell contents).

    That will give you the truncated list.
  15. Replies

    Re: Copy Conditional formats only

    Rather than copying and pasting, if you go into the conditional format and choose Manage Rules, you can set multiple ranges to use the same rules as the original range. Just use Ctrl when selecting...
  16. Re: Checking column headers on data extract - is there a better way

    There *should* only be one instance of each column name, although the same column can have different names from extract to extract depending on who produces it. I do have a list of standard column...
  17. Re: Checking column headers on data extract - is there a better way

    It could be that the headers are correct, but not in the right order, but it could also be:
    - wrong headers in the right order
    - wrong headers in the wrong order
    - missing columns that are not...
  18. Checking column headers on data extract - is there a better way

    I receive a number of data files each month from a number of different sources, and while they are all supposed to be in a standard layout (column headers and column order), they often aren't.
  19. Replies

    Re: Increment based on Adjecent Cells

    Make sure that your data has a header row (so your value of A123 would be in cell A2).

    In B2, type the following formula, then copy down:

    What it's doing is checking if the...
  20. Re: Add calculated field based on alternating row values

    Wow, that's quite a lot to think about. I need to read up a LOT more on this subject.

    Thank you so much for your help with this.
  21. Re: Add calculated field based on alternating row values

    If the direction on the row is "outbound" and the next row's direction is "inbound", then populate FROM with the outbound email from the row and populate TO with the inbound email from the next row. ...
  22. Add calculated field based on alternating row values

    I have a data table that looks something like this (fake data obviously) that's been pulled from a text file and brought into Excel using the Power Query AddIn for Excel 2013.

  23. Re: Keep rows containing specified text strings

    Panic over, I've found how to do it!

    Edit Query, and THEN click the filter box in the top right and apply whatever parameters are required for your filtering. Microsoft could have made that a bit...
  24. Keep rows containing specified text strings

    Apologies if this is a "newb" question, but Power Query is completely new to me and I've no idea how to go about this...

    I have a text file which is data dump from our email client and contains...
  25. Re: Code to return name of macro that's just been run

    I can do that, but I was hoping for an automatic method, as I have more than 40 different macros to set up and I thought it would be easier to have 1 line that could reference the Sub name without me...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4