Forum Discussion

JMarshall325's avatar
JMarshall325
Copper Contributor
Aug 11, 2020

Auto fill columns across an Excel table

Good afternoon, I need to fill in the Document ID columns in a table so that each Tag Term has a Document ID associated with it. The Document IDs should only fill in where there is a corresponding T...
excel
Macros and VBA
mathetes's avatar
mathetes
Silver Contributor
Aug 11, 2020

JMarshall325 

 

The formula that you see in the formula bar here will do it--just copy down, to however many rows the max would be, and then copy/paste those cells to the alternating columns the rest of the way across your 2000 columns. That's how I did it in the file I created to demonstrate.

 

I figure a half-second for each copy/paste and that would take just over 8 minutes. It would probably take longer to write and execute a macro.

 

  • JMarshall325's avatar
    JMarshall325
    Copper Contributor
    Aug 11, 2020

    mathetes 

    Thanks for your speedy reply. One more question - how would you repeat the formula to repeat the action.

    Would it be like so the following?

     

             =IF(B3<>"",A2,""),(D3<>"",C2,""),(F3<>"",E2,"")    

     

    Regards,

    JMarshall

    • mathetes's avatar
      mathetes
      Silver Contributor
      Aug 11, 2020

      JMarshall325 

       

      Did you try copy and paste?  I'm going to make an assumption here, based on the question: you seem to be very much a beginner with Excel, and maybe even not confident with computers. So without wanting to be too basic, I'm going to spell out steps.

       

      First of all, you can copy and paste from the initial cell (ie., copy A3) and then highlight the cells below it, as far as you want to go, and do a "paste." The formula will automatically adjust the references because I used relative, not absolute, references.

       

      And the same is true when you copy to cell C3 and the cells below that. You do not need to re-write the formula. Copy it and paste it.

       

      And even better, you can use keyboard short-cuts CTRL+C to copy, CTRL+V to paste. Or, if you're in a Mac (as I am) Command+C to copy, Command+V to paste. And you can copy all of the range A3:A10 or whatever to C3:C10...   Those keyboard short-cuts are what I was assuming would be used when I said it would go quickly.

       

      Postscript: I apologize for not including the spreadsheet itself. I didn't even save it because I thought those copy and paste steps were so basic that you'd know how to proceed. I could re-create it if you need that level of assistance.

      • JMarshall325's avatar
        JMarshall325
        Copper Contributor
        Aug 13, 2020

        mathetes 

        Thanks for the clarification. I've worked with Excel quite a bit, but I'm self-taught, so I have a lot of gaps in my knowledge, particularly with regards to writing formulas and code. I was not aware that the formula that you sent had relative references rather than the absolute reference.

        I would still like to figure out how to replicate the action across the entire spreadsheet rather than manually copying and pasting.

        8 minutes is not a lot of time, but I'm very likely going to have more of the spreadsheets to work with so I'm trying to automate as much as I can.

        Thanks very much for your suggestions - they will be a big help.

Resources