Auto fill columns across an Excel table

Question

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 Term Tag in the column to the right of it. The table has 2000 paired columns, so can someone tell me how I can do that automatically - like with a macro for example? The Starting Table sample is what the table looks like now. The Finished Table sample is how I want the table to be filled in.&nbsp;STARTING TABLE SAMPLE&nbsp;Document IDTerm tagDocument IDTerm tagDocument IDTerm tag145436&nbsp;145441&nbsp;145461&nbsp;&nbsp;National Criminal Justice Reference Service (NCJRS)&nbsp;Students&nbsp;Fellowship Programs&nbsp;Grants.gov (website)&nbsp;Grants.gov (website)&nbsp;&nbsp;&nbsp;Technical assistance (TA)&nbsp;Fellowship Programs&nbsp;&nbsp;&nbsp;Forensic sciences&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxFINISHED TABLE SAMPLEDocument IDTerm tagDocument IDTerm tagDocument IDTerm tag145436&nbsp;145441&nbsp;145461&nbsp;145436National Criminal Justice Reference Service (NCJRS)145441Students145461Fellowship Programs145436Grants.gov (website)145441Grants.gov (website)&nbsp;&nbsp;145436Technical assistance (TA)145441Fellowship Programs&nbsp;&nbsp;145436Forensic sciences&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks in advance.Regards,JMarshall&nbsp;&nbsp;

mathetes · Answer

JMarshall325&nbsp;&nbsp;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.&nbsp;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.&nbsp;

jmarshall325 · Answer

mathetes&nbsp;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?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =IF(B3&lt;&gt;"",A2,""),(D3&lt;&gt;"",C2,""),(F3&lt;&gt;"",E2,"")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Regards,JMarshall

mathetes · Answer

JMarshall325&nbsp;&nbsp;Did you try copy and paste?&nbsp; 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.&nbsp;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.&nbsp;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.&nbsp;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...&nbsp;&nbsp; Those keyboard short-cuts are what I was assuming would be used when I said it would go quickly.&nbsp;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 · Answer

mathetes&nbsp;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.

mathetes · Answer

JMarshall325&nbsp;&nbsp;I wish I could help more. I don't write macros myself; am something of a cynic with regard to them (although I know that there are times when they can be very useful)....I just have the experience that Excel generally has the ability to do a lot of things with its built-in functions.&nbsp;I tried just now to record the macro copying cells two columns to the right, but can't get it to do that "generically"... so it would definitely take me more than the eight minutes.&nbsp;When you say you're likely to have more of these spreadsheets to work with I find myself wondering if there's something you can do to alter the way they come to you. It would be a LOT easier to create the kind of relationship you're creating if it all came organized in two parallel columns rather than spread across 2000 columns.&nbsp; Is it possible to do something to preclude the need to do this in the first place?

Forum Discussion

Auto fill columns across an Excel table

6 Replies

Resources

Document ID	Term tag	Document ID	Term tag	Document ID	Term tag
145436		145441		145461
	National Criminal Justice Reference Service (NCJRS)		Students		Fellowship Programs
	Grants.gov (website)		Grants.gov (website)
	Technical assistance (TA)		Fellowship Programs
	Forensic sciences