*Official* Excel help thread Page 4

  • Page

    of 10 First / Last

  • Merlinho 9 Dec 2009 16:31:50 5,909 posts
    Seen 12 months ago
    Registered 9 years ago
    Boffins assemble!

    Help needed with a tricky problem.

    A client has given me an Excel file which has records split across 3 rows. What I need to do is move data on row 2 up to about column 10 on row 1, then move data on row 2 up to column 20 on row 1. Then continue this moving rows 5&6 up to row 4, rows 8&9 up to row 7 etc repeat until the end of the spreadsheet (it's a good few hundred lines). I really can't think of a solution...help me boffins!
  • Merlinho 10 Dec 2009 09:03:27 5,909 posts
    Seen 12 months ago
    Registered 9 years ago
    Desperate bump for the morning crowd.

    Edit: actually forget it, even this won't solve it as some records span more than 3 rows. Cursed Journal entries...
  • Blaketown 10 Dec 2009 09:09:58 4,630 posts
    Seen 3 days ago
    Registered 7 years ago
    Sounds like the sort of thing you could do with vb code.

    Couldn't you record the process for the first move as a macro and then do a bit of rejigging of the code to repeat it over and over.

    Brap, brap, old chap.

  • Merlinho 10 Dec 2009 09:14:06 5,909 posts
    Seen 12 months ago
    Registered 9 years ago
    Cheers, I just realised that some records span more than 3 lines if they have transactions between more than two accounts in them, so maybe Excel won't be able to fix it. I'm now saving it as a text file and trying to recognise it in our whizzy audit software this way.
  • Psychotext 10 Mar 2010 22:12:23 52,791 posts
    Seen 13 seconds ago
    Registered 8 years ago
    Bad time of night to ask... but maybe someone with the knowledge is around, or maybe I can bump it in the morning.

    Say I have a workbook with X sheets. Each sheet has a certain range that contains names in cells A1:A49 and C1:C49. I want to have another sheet that contains a distinct list of the names contained in the workbook... anyone got a simple solution?

    I don't mind adding each sheet name to the formula manually whenever I add one.
  • jellyhead 10 Mar 2010 22:25:52 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    I don't have excel to hand but i've got an idea it's pretty simple. I've written scripts to do similar things so i'll have a poke about.

    Bump again in the morning if i don't reply back soon.

    This signature intentionally left blank.

  • jellyhead 10 Mar 2010 22:29:45 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    Extract Data to Another Worksheet. Uses the "advanced filter" dialog rather than a macro though.

    This signature intentionally left blank.

  • jellyhead 10 Mar 2010 22:32:51 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    GenericCollationCodeForCopyingByDistinctRowData
    Some vba code you could modify.

    I'm just googling and filtering the garbage so my links might need some work but hopefully you'll get some ideas that are useful.

    This signature intentionally left blank.

  • jellyhead 10 Mar 2010 22:36:41 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    [code]
    Another way to extract the uniques list dynamic in another sheet
    using simpler non-array formulas ..

    Source data in Sheet1's col C (file_name), running in C2 down

    In another sheet,

    Put in A2:
    =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!$C$2:C2,Sheet1!C2)>1,"",ROW()))
    Leave A1 blank

    In B2:
    =IF(ROW(A1)>COUNT(A:A),"",INDEX(Sheet1!C:C,SMALL(A:A,ROW(A1))))
    Select A2:B2, fill down to the max expected extent of data in Sheet1's col
    C. Hide away col A. The uniques list of file_names will be extracted in col
    B, all neatly bunched at the top.
    [/code]

    From egghead cafe.

    This signature intentionally left blank.

  • Psychotext 10 Mar 2010 22:37:39 52,791 posts
    Seen 13 seconds ago
    Registered 8 years ago
    I can do it with the advanced filter, but unless I could save the filter conditions each time it wouldn't be of much use to me. I found this:

    http://www.ozgrid.com/forum/showthread.php?t=86452

    But as best I can tell the solutions posted don't actually work across multiple ranges. Works fine for a single range though.

    Edit - I'll give that last one a look
  • Psychotext 10 Mar 2010 22:48:12 52,791 posts
    Seen 13 seconds ago
    Registered 8 years ago
    Actually, it's a bit of a hack but I've just realised how I could get that one I posted to work for me. :)
  • jellyhead 10 Mar 2010 22:52:45 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    Cool, you won't be needing the microsoft page on controlling Autofilter with Macros then :(

    This signature intentionally left blank.

  • jellyhead 10 Mar 2010 22:55:17 24,350 posts
    Seen 9 months ago
    Registered 11 years ago
    From your link - OzGrid - VBA for Autofilter.
    Horrid looking site but some good info, nice find.

    This signature intentionally left blank.

  • Psychotext 10 Mar 2010 22:57:38 52,791 posts
    Seen 13 seconds ago
    Registered 8 years ago
    Interesting stuff. Thanks for your assistance. :)
  • keyboardmonkey 19 Mar 2010 12:06:48 409 posts
    Seen 3 years ago
    Registered 7 years ago
    Afternoon all, i could do with a little bit of help/ ideas.

    I'd like to create a table that has the sheet names on it and checkboxes next to it e.g.
    Sheet 2
    Sheet 3 X
    Sheet 4

    When i put a check next to Sheet 3 i would like that Sheet to become visible (Not get focus, but just become visible), and if i remove the X i would like it to be hidden again.

    Anyway to do this without using Macros or writing a bit of VB ?
  • otto Moderator 6 Jul 2010 12:00:26 49,291 posts
    Seen 2 weeks ago
    Registered 12 years ago
    Quick question. I have a formula which I need to repeat across a range of cells. Some of the variables I'm calling are adaptive and others are constant. Maybe it's just easier if I demonstrate:

    fx = B7-(B13-B12)

    I select the box and drag it down across several rows, and the cell references change accordingly, but I don't want B13 to change - I want that reference to stay constant; but I still need to call the cell. In other words, instead of

    fx = S7-(S13-S12)

    I want

    fx = S7-(B13-S12)

    Is there a way of doing this automagically?

    say no to Eurogamer sigs

  • otto Moderator 6 Jul 2010 12:03:08 49,291 posts
    Seen 2 weeks ago
    Registered 12 years ago
    Ah, it's OK, I've found it. Basic noob error. 'Insert' -> 'Name' -> 'Define'

    say no to Eurogamer sigs

  • MMMarmite 6 Jul 2010 12:03:15 911 posts
    Seen 8 hours ago
    Registered 7 years ago
    Prefix it with $'s? IIRC $B$13 would keep the cell reference static.
  • X201 6 Jul 2010 12:03:34 14,698 posts
    Seen 12 hours ago
    Registered 8 years ago
    Stick a Dollar $ sign in front of the B and the 13
  • X201 6 Jul 2010 12:04:49 14,698 posts
    Seen 12 hours ago
    Registered 8 years ago
    otto wrote:
    Ah, it's OK, I've found it. Basic noob error. 'Format' -> 'Name' -> 'Define'

    You can do it that way too.

    But we were keeping that for when you get onto the Intermediates course Otto ;)


  • BravoGolf Moderator 6 Jul 2010 12:06:09 12,734 posts
    Seen 1 day ago
    Registered 11 years ago
    Otto - try using $B13, that anchors it.

    I too have a question. Currently I have a report of all projects in a Excel sheet however those higher up than I need a report in powerpoint. It's an absolutely pain in the arse manually copying the excel and it means any updates that happen in the interim have to be replicated at both reports. The exec still want powerpoint though, the feckers. So, is there a way to automatically export the contents of excel into powerpoint and span it over multiple pages rather than carry on off the edge?
  • otto Moderator 6 Jul 2010 12:06:13 49,291 posts
    Seen 2 weeks ago
    Registered 12 years ago
    lol cheers guys

    say no to Eurogamer sigs

  • MMMarmite 6 Jul 2010 12:10:32 911 posts
    Seen 8 hours ago
    Registered 7 years ago
    We're like buses ;)
  • BravoGolf Moderator 6 Jul 2010 12:12:16 12,734 posts
    Seen 1 day ago
    Registered 11 years ago
    EG is awesome. Now, awesome EG, answer my question post haste.
  • Lutz 6 Jul 2010 12:21:54 48,854 posts
    Seen 10 months ago
    Registered 11 years ago
    Surely you need the official powerpoint help thread here? ;)
  • Alastair 6 Jul 2010 12:28:58 14,953 posts
    Seen 12 hours ago
    Registered 12 years ago
    BravoGolf wrote:
    Otto - try using $B13, that anchors it.

    I too have a question. Currently I have a report of all projects in a Excel sheet however those higher up than I need a report in powerpoint. It's an absolutely pain in the arse manually copying the excel and it means any updates that happen in the interim have to be replicated at both reports. The exec still want powerpoint though, the feckers. So, is there a way to automatically export the contents of excel into powerpoint and span it over multiple pages rather than carry on off the edge?

    Can't you embed the Excel sheet (or parts of it) into the PPT slide(s)?
    Err, that's not really helping as I'm sure you knew that much already.

    Not as nice as I used to be

  • w00t 19 Jul 2010 11:45:19 10,976 posts
    Seen 21 hours ago
    Registered 9 years ago
    OK, I'm a spoon. Somehow I've vanished the bar that shows what text is in a specific cell (or whatever it's fancy name is - Monday morning, leave me alone).

    How do I get it back?

    Edit: 2007, btw

    Edit 2: nvm, found it

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • StringBeanJean 6 Sep 2010 16:39:49 1,775 posts
    Seen 2 days ago
    Registered 7 years ago
    HELP

    I have an excel document (just a table really) which I have fucked up beyond belief and then hit save and closed down. Is there any way of returning to a particular date and time on a document and recovering from there?? It's a big table and when I tried to sort by alphabet it offered me the opportunity to sort the adjacent columns with it. i should have hit yes. I didn't realise mistake and then saved and closed. Is ther anything I can do. I won't go any more specific but it will mean a 3 hour job if not possible.

    HELP

    Edit: I'm using Excel 2007
  • Chimpus 6 Sep 2010 16:45:37 751 posts
    Seen 1 month ago
    Registered 10 years ago
    locate the autosave folder maybe?

    also if you rightclick >properties the file it may have a previous versions tab?
  • StringBeanJean 6 Sep 2010 16:58:37 1,775 posts
    Seen 2 days ago
    Registered 7 years ago
    Neither seem to work. Bugger.
  • Page

    of 10 First / Last

Log in or register to reply