Banding together cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2000007%22%20slang%3D%22en-US%22%3EBanding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000007%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20for%20my%20club%3B%20each%20member's%20profile%20is%20spread%20down%205%20rows%2C%20then%20across%2010%20columns.%26nbsp%3B%20I%20would%20like%20to%20attach%20all%20the%20members%20rows%20and%20columns%20(their%20profile)%20together%2C%20to%20be%20able%20to%20move%20and%20sort%20the%20profile%20as%20one.%26nbsp%3B%20Example%20A1%20has%20a%20name%20A2%20has%20address%20A3%20Telephone%20%23%3B%20B1%20has%20dues%2C%20B2%20has%20locker%20%23%2C%20C1%20has%20nickname%20and%20so%20on.%26nbsp%3B%20I%20would%20like%20to%20be%20able%20to%20sort%20the%20entire%20profile%20by%20Telephone%20%23%2C%20by%20Locker%20or%20by%20any%20of%20the%20other%20data.%26nbsp%3B%20I%20hope%20I%20explained%20this.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2000007%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2000403%22%20slang%3D%22en-US%22%3ERe%3A%20Banding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890140%22%20target%3D%22_blank%22%3E%40Stephen718%3C%2FA%3E%26nbsp%3BI%20would%20recommend%20you%20restructure%20(if%20feasible)%20the%20data%20and%20store%20all%20member%20information%20on%20one%20row%20over%2050%20columns.%20That's%20not%20much%20of%20a%20deal%20for%20Excel.%3C%2FP%3E%3CP%3EThen%20you%20can%20easily%20do%20the%20sorting%20and%20filtering%20with%20the%20press%20of%20a%20button.%20And%20should%20you%20need%20to%20have%20an%20overview%20of%20all%20information%20(i.e.%20from%20all%2050%20columns)%20for%20one%20particular%20member%2C%20you%26nbsp%3Bcould%20design%20a%20neat%20%22Member%20Card%22%20in%20a%20separate%20sheet%20and%20link%20it%20to%20all%20the%2050%20fields%20in%20the%20database%20by%20using%20one%20of%20Excels%20lookup%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2000993%22%20slang%3D%22en-US%22%3ERe%3A%20Banding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20the%20reply%20but%20one%20column%20is%20not%20doable%3B%20I%2C%20as%20a%20hobby%2C%20with%20no%20training%20at%20all%2C%20made%20a%20very%20convoluted%20spreadsheet.%26nbsp%3B%20I%20have%20been%20working%20on%20it%20for%20a%20few%20years%2C%20adding%20features%20as%20I%20learn.%20I'm%20sure%20someone%20with%20training%20could%20have%20made%20this%20spreadsheet%20in%20a%20few%20hours%20and%20made%20a%20much%20better%20one%20to%20boot%2C%20but....%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%20don't%20know%20if%20I%20need%20to%20make%20a%20field%2C%20a%20table%20or%20something%20else%20that%20connects%20several%20cells%20together.%26nbsp%3B%20Thank%20you%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2001072%22%20slang%3D%22en-US%22%3ERe%3A%20Banding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890140%22%20target%3D%22_blank%22%3E%40Stephen718%3C%2FA%3E%26nbsp%3BFair%20enough%2C%20but%20if%20you%20are%20willing%20to%20learn%20something%20new%2C%20I%20promise%20you%20it%20will%20not%20take%20years%20and%20it%20will%20save%20you%20a%20lot%20of%20time%20in%20the%20future.%20Perhaps%20you%20think%20it's%20difficult%20to%20re-structure%20the%20member%20data%20from%2010%20columns%20by%205%20rows%20to%2050%20columns%20on%201%20row.%20In%20fact%20it's%20not.%20Not%20sure%2C%20though%2C%20how%20it%20will%20impact%20the%20other%20features%20in%20your%20schedule.%20If%20you%20want%20to%20give%20it%20a%20try%2C%20perhaps%20you%20can%20upload%20a%20part%20of%20your%20schedule%20with%20data%20for%2C%20say%205%20members%20(anonymised%2C%20of%20course)%20and%20indicating%20what%20type%20of%20features%20are%20needed.%26nbsp%3BUp%20to%20you%20to%20dare%20and%20make%20a%20change.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2001306%22%20slang%3D%22en-US%22%3ERe%3A%20Banding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BI'm%20game!%20I%20attached%20a%20file%20that%20I%20made%20to%20try%20and%20explain%20each%20cell's%20function.%20I%20appreciate%20any%20comments%3B%20good%20or%20bad.%26nbsp%3B%20This%20file%20is%20a%20stripped%20down%20version%20but%20it%20conveys%20the%20concept.%26nbsp%3B%20Thanks%20again%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2001342%22%20slang%3D%22en-US%22%3ERe%3A%20Banding%20together%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BI%20forgot%20to%20explain%20what%20I%20want%20to%20do%3B%26nbsp%3B%20Let's%20say%20I%20want%20to%20sort%20the%20profile%20by%20locker%20number%20or%20balance%20due.%26nbsp%3B%20Thanks%20again%3B%20I've%20been%20waiting%20a%20long%20time%20to%20get%20an%20opinion%20on%20my%20efforts%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a spreadsheet for my club; each member's profile is spread down 5 rows, then across 10 columns.  I would like to attach all the members rows and columns (their profile) together, to be able to move and sort the profile as one.  Example A1 has a name A2 has address A3 Telephone #; B1 has dues, B2 has locker #, C1 has nickname and so on.  I would like to be able to sort the entire profile by Telephone #, by Locker or by any of the other data.  I hope I explained this. 

9 Replies

@Stephen718 I would recommend you restructure (if feasible) the data and store all member information on one row over 50 columns. That's not much of a deal for Excel.

Then you can easily do the sorting and filtering with the press of a button. And should you need to have an overview of all information (i.e. from all 50 columns) for one particular member, you could design a neat "Member Card" in a separate sheet and link it to all the 50 fields in the database by using one of Excels lookup functions.

@Riny_van_Eekelen  Thank you for the reply but one column is not doable; I, as a hobby, with no training at all, made a very convoluted spreadsheet.  I have been working on it for a few years, adding features as I learn. I'm sure someone with training could have made this spreadsheet in a few hours and made a much better one to boot, but....     I don't know if I need to make a field, a table or something else that connects several cells together.  Thank you again

@Stephen718 Fair enough, but if you are willing to learn something new, I promise you it will not take years and it will save you a lot of time in the future. Perhaps you think it's difficult to re-structure the member data from 10 columns by 5 rows to 50 columns on 1 row. In fact it's not. Not sure, though, how it will impact the other features in your schedule. If you want to give it a try, perhaps you can upload a part of your schedule with data for, say 5 members (anonymised, of course) and indicating what type of features are needed. Up to you to dare and make a change.

@Riny_van_Eekelen   I'm game! I attached a file that I made to try and explain each cell's function. I appreciate any comments; good or bad.  This file is a stripped down version but it conveys the concept.  Thanks again 

@Riny_van_Eekelen   I forgot to explain what I want to do;  Let's say I want to sort the profile by locker number or balance due.  Thanks again; I've been waiting a long time to get an opinion on my efforts

@Stephen718 Thanks for uploading. Wow, I had not foreseen this. Give me some time to digest it. Will get back to you, if not someone else does that before me.

@Stephen718 After having looked at your schedule I can say that it combines basic membership registration, accounting tasks and monthly reporting. Dues and fees are hard-coded into sub-headers for each member. All in one monster of a table with an incredible number of formulae and performing very slow (at least on my machine). And, since the file you uploaded only represents a stripped down version of the real one, I wonder how that one runs. 

 

Please have a look at a very quick-and-dirty and far from perfect approach that I included in "Sheet1". It contains tables for Members, Membership Types, Lockers and Payments Received. Some of the numbers are made-up and I did not include all members/lockers from your Dues-sheet. But, from those four tables you can create all sorts of reports as demonstrated (row 24 and down). And this is only the tip of the iceberg.

 

You can imagine that if you could somehow transform your real data into structured tables like in my example, your life will be a lot easier. Adding new members is done on one table. Assigning a locker to a member in another. When the dues change next year, just update a small table once and all calculations are updated instantly. Much cleaner and less prone to error.

 

Developing a working system will take some effort but that goes beyond the scope of this forum. Anyhow, I hope that you will find some inspiration from my examples and that it will help you re-design your schedules. 

@Riny_van_Eekelen   Thank you so much; yes, it is very labor intensive, but prior to my spreadsheet, the process was done with pencil and recorded in a ledger that was the size of a small car.   If you saw the entire sheet , it has a number of built in formulas to detect errors ( not foolproof by any means but adequate )  I am now inspired to take a class to learn the best way to do my "hobby". Thank you for your time, it is appreciated. 

 

FYI: the members think I am an Excel genius, only because they are a lot less knowledgeable. I've said many times that a "pro" could make a much much better spreadsheet. I will attempt it. 

@Stephen718 Good luck!