Forum Discussion
Auto fill columns across an Excel table
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.
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
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.
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.
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:
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:
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."