Auto fill columns across an Excel table

Copper Contributor
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.
 
STARTING TABLE SAMPLE
 
Document IDTerm tagDocument IDTerm tagDocument IDTerm 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    
 
 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
FINISHED TABLE SAMPLE
Document IDTerm tagDocument IDTerm tagDocument IDTerm tag
145436 145441 145461 
145436National Criminal Justice Reference Service (NCJRS)145441Students145461Fellowship Programs
145436Grants.gov (website)145441Grants.gov (website)  
145436Technical assistance (TA)145441Fellowship Programs  
145436Forensic sciences    
 
 
Thanks in advance.
Regards,
JMarshall
 

 

6 Replies

@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.

mathetes_0-1597170501131.png

 

@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

@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.

@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.

@JMarshall325 

 

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.

 

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.

 

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.  Is it possible to do something to preclude the need to do this in the first place?

@JMarshall325 

 

Can I probe a bit more here into how you've used Excel, or maybe more precisely, toward what ends. This little snippet of the spreadsheet you posted in requesting help appears to be something from the academic realm. It also could just be the tip of an iceberg--and maybe this is what you're alluding to in saying you expect you're "likely going to have more of the spreadsheets to work with so..."

 

I ask because, although the heading for this whole thread refers to "columns across an Excel table," it's NOT actually an Excel Table. In an Excel Table, all of that would have been arrayed thus, and could form the content on a database "tab" as I've done in the attached demo file:

mathetes_0-1597323158472.png

From that as the source database, you could develop an extraction protocol--and this is just a demonstration--where the simple selection of an ID (from a list of unique IDs generated from the database itself) would immediately display all the information associated with that ID. As shown below:

mathetes_1-1597323329246.png

The data extracted here is all of what's available; were that database to be extended, it could show much, much more. Depending on the nature of your data, stats, activities, etc., there are data extraction tools available (e.g., the Pivot Table) for cross-tabulated reports and graphs....

 

A word of warning: this demonstration spreadsheet uses the most recent release of Excel and two functions available in it to all users who have it (I'm not an "insider" with beta access to features still under development)...those being the functions UNIQUE and FILTER in this case. So if your display does not work in all respects, it would be because you are still working with an older version of Excel.

 

Anyway, I am showing you this because I am inferring from what you've read that (just as I discovered some of the holes in my own grasp of Excel's features in coming to this board after having retired nearly twenty years ago)....I suspect you may have been using Excel at a level far below its capabilities to help you in your research (?) or work, whatever it is.

 

It's why, if you could get that original data organized as a genuine table, you'd be on the verge of unlocking some of the real powers of Excel to help you "mine the data."