Microsoft Excel
--
Questions
--
Followers
Top Experts
EXCEL: Link tables together for sorting data differently.
(This doesn't have to be in Excel, but that's what I'll use in this example.)
Say I've got a table. I'm trying to create a second table that has the same data, right next to it, on the same sheet. (Doesn't have to be on the same sheet if that's your solution, just preferred to be.)
Just for the sake of the example, say the table is very simple; just a few columns.
I'm trying to link these two tables together. All the data is the same.
The only difference between these two tables is:
Table #1: sort on column one
Table #2: sort on column two
Same data in both. Any modifications on Table #1 should propogate to Table #2. Mainly cell data modifcations, but also nice if included Row additions and deletions, though I can deal if this is not your solution, though I do need some way to do this. Also ok if you only change table #1 and changes go to table #2, I don't really need to have the modifications propogate both ways (nice but unnecessary). The idea here is to keep the data consistent between the two tables. It would be nice if the tables were live, that is, so I didn't have to perform some update to keep the data consistent. (Nice but not necessary.)
Any ideas?
I've tried just doing regular cell references, to the point of even getting table-like column headers in that second table. The data updates great from #1 to #2. They even have the dropdown sorting menus. Trouble is, they don't do anything; selecting the sorting methods does not produce a result. (Why even have the menu there..? Excel bug?) And when Table #1 resorts, it resorts table #2. I've made other attempts, but this is really as far as I've gotten.
Should be so simple... Trying not to use Access, VB/A (or other programming language), or PowerPivot. Could consider PowerPivot if abolutely necessary, if it's the only way to solve this problem. But really just trying to go regular Excel. It would be really great if I could duplicate this solution to other platforms, such as with OpenOffice or GoogleDocs, but this isn't necessary.
Simple Excel question.
Thanks!
Coder
ps - I'm using Excel 2010, but like I say, I can be flexible there.
[Edit: Added PS to clarify using Excel 2010.]
Say I've got a table. I'm trying to create a second table that has the same data, right next to it, on the same sheet. (Doesn't have to be on the same sheet if that's your solution, just preferred to be.)
Just for the sake of the example, say the table is very simple; just a few columns.
I'm trying to link these two tables together. All the data is the same.
The only difference between these two tables is:
Table #1: sort on column one
Table #2: sort on column two
Same data in both. Any modifications on Table #1 should propogate to Table #2. Mainly cell data modifcations, but also nice if included Row additions and deletions, though I can deal if this is not your solution, though I do need some way to do this. Also ok if you only change table #1 and changes go to table #2, I don't really need to have the modifications propogate both ways (nice but unnecessary). The idea here is to keep the data consistent between the two tables. It would be nice if the tables were live, that is, so I didn't have to perform some update to keep the data consistent. (Nice but not necessary.)
Any ideas?
I've tried just doing regular cell references, to the point of even getting table-like column headers in that second table. The data updates great from #1 to #2. They even have the dropdown sorting menus. Trouble is, they don't do anything; selecting the sorting methods does not produce a result. (Why even have the menu there..? Excel bug?) And when Table #1 resorts, it resorts table #2. I've made other attempts, but this is really as far as I've gotten.
Should be so simple... Trying not to use Access, VB/A (or other programming language), or PowerPivot. Could consider PowerPivot if abolutely necessary, if it's the only way to solve this problem. But really just trying to go regular Excel. It would be really great if I could duplicate this solution to other platforms, such as with OpenOffice or GoogleDocs, but this isn't necessary.
Simple Excel question.
Thanks!
Coder
ps - I'm using Excel 2010, but like I say, I can be flexible there.
[Edit: Added PS to clarify using Excel 2010.]
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
I looked at your example, didn't happen to see any formulas, but I think your comment here might be making a little more progress in my brain.
It almost seems to me like what you are saying is that I'll really need 3 tables: (1) the original, containg everything and sorted by, say, column 1; (2) a table that has no data except for the data in the column to which to key off of for the alternative sorting; and (3) on another sheet, a table which utilizes #2 via formulas which are in each cell.
(Do I have the idea right? Or are there just 2 tables..?)
Not following you just yet.
It almost seems to me like what you are saying is that I'll really need 3 tables: (1) the original, containg everything and sorted by, say, column 1; (2) a table that has no data except for the data in the column to which to key off of for the alternative sorting; and (3) on another sheet, a table which utilizes #2 via formulas which are in each cell.
(Do I have the idea right? Or are there just 2 tables..?)
Not following you just yet.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
I see them. Vlookups everywhere, pointing to table1.
But then how can I sort here? If I'm trying to get one of them Sorted on Column #1 ("Keyfield"/B) and the other sorted on Column #2 ("Data 1"/C), it seems like I can't. Actually, it seems that neither table can utilize the column sorting mechanisms.
Regardless, that is very interesting, I think you've really pointed some things out to me here. And referring to the table in that manner it almost seems like you wouldn't be forced to be on two separate sheets if you didn't want to. (Although, for the things I'm doing, I do want separate sheets as in your example.)
Any ideas on the sorting..?
But then how can I sort here? If I'm trying to get one of them Sorted on Column #1 ("Keyfield"/B) and the other sorted on Column #2 ("Data 1"/C), it seems like I can't. Actually, it seems that neither table can utilize the column sorting mechanisms.
Regardless, that is very interesting, I think you've really pointed some things out to me here. And referring to the table in that manner it almost seems like you wouldn't be forced to be on two separate sheets if you didn't want to. (Although, for the things I'm doing, I do want separate sheets as in your example.)
Any ideas on the sorting..?
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Thanks everyone. I'm reviewing all your great help. I'm sure I'll assign points tomorrow, just can't do this at this time. Thanks!
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Ok, I've finally had a chance to look into what you two were actually saying here and gotten everything working and I *believe* I understand it all.
Here's my thoughts:
Barman: Just what I was looking for. That really is great. Yes, a little annoying on the insert and delete, but it's what I was going for. I think I wasn't understanding what you were saying at first because I was looking at the Sheet 1 master table and not looking at the values in the follower table on Sheet 1 (2). I've since understood this and also gotten this working using my own tables, even on the same page thanks to your generous directions. Thank you. My only question: you subtract 1 from the COLUMN() value because the table is offset by one to the right, right?
Harfang: Thanks very much for (1) fixing the FALSE value (and right like wouldn't you always want FALSE if you're going after an exact value on what is essentially a primary key?) I wasn't able to get your second group of data sorted after I turned them into tables, but that's kind of irrevelant because Barman already showed it. Also, thanks for putting the reminders in the workbook (I think you meant "on the right" on the last sentence there but I totally got what you were driving at.) You made it easier. And, love the sig.
So, in the end, Excel tables really are kind of interesting. I also really like how in Excel you can delete rows in a table without affecting other parts of the spreadsheet, like another table to the right. You learn something new every day. Nice.
Thanks guys.
Here's my thoughts:
Barman: Just what I was looking for. That really is great. Yes, a little annoying on the insert and delete, but it's what I was going for. I think I wasn't understanding what you were saying at first because I was looking at the Sheet 1 master table and not looking at the values in the follower table on Sheet 1 (2). I've since understood this and also gotten this working using my own tables, even on the same page thanks to your generous directions. Thank you. My only question: you subtract 1 from the COLUMN() value because the table is offset by one to the right, right?
Harfang: Thanks very much for (1) fixing the FALSE value (and right like wouldn't you always want FALSE if you're going after an exact value on what is essentially a primary key?) I wasn't able to get your second group of data sorted after I turned them into tables, but that's kind of irrevelant because Barman already showed it. Also, thanks for putting the reminders in the workbook (I think you meant "on the right" on the last sentence there but I totally got what you were driving at.) You made it easier. And, love the sig.
So, in the end, Excel tables really are kind of interesting. I also really like how in Excel you can delete rows in a table without affecting other parts of the spreadsheet, like another table to the right. You learn something new every day. Nice.
Thanks guys.
You cannot use array formulas in a table, sadly. My example is a good solution if there is no key column, and it is probably more efficient on large lists, as it doesn't rely on VLOOKUP. If your list is small and if you have a ID, The Barman's solution is easier to maintain.
I had in fact tried his solution in my worksheet, with a different COLUMN calculation. I've now updated the instructions for DELETE, INSERT, and REPAIR(!); perhaps you'll like it. The tables are named MASTER and VIEW.
Thanks for the detailed feedback, it's always a pleasure to see what becomes of our comments and suggestions.
Success with your project!
(°v°)
Q-27809170.xlsx
I had in fact tried his solution in my worksheet, with a different COLUMN calculation. I've now updated the instructions for DELETE, INSERT, and REPAIR(!); perhaps you'll like it. The tables are named MASTER and VIEW.
Thanks for the detailed feedback, it's always a pleasure to see what becomes of our comments and suggestions.
Success with your project!
(°v°)
Q-27809170.xlsx
Indeed, always happy to help, it is because the table is one to the right that I have column - 1 in the lookup.
You can use other formula than VLookup (which can be a bit slow):
Index & Match are good
Sumif & Countif are great
in all, I am glad you are back on the right track.
:)
You can use other formula than VLookup (which can be a bit slow):
Index & Match are good
Sumif & Countif are great
in all, I am glad you are back on the right track.
:)
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Barman, I will have to look those up -- I really haven't looked at these kinds of Excel functions that much. Do you know if there is a preference between those solutions, or one way that is typically preferred? (I hear a lot of people talk about using VLOOKUPs.) Or are they about equal ways to approach the problem? (Is VLOOKUP more efficient?)
Harfang, I have your and Barman's examples in my folder to keep this in mind! Thanks for the update, and the repair idea. That makes so much sense -- the functions will automatically update assuming that key field is unique.
(Someday I'll have to work the macro angle and get this all working that way also!)
I hope everyone thought the point distribution was fair, I hardly have this down to a science.
Thanks again to both of you.
Harfang, I have your and Barman's examples in my folder to keep this in mind! Thanks for the update, and the repair idea. That makes so much sense -- the functions will automatically update assuming that key field is unique.
(Someday I'll have to work the macro angle and get this all working that way also!)
I hope everyone thought the point distribution was fair, I hardly have this down to a science.
Thanks again to both of you.
About MATCH and INDEX... You can always rewrite VLOOKUP with them, e.g.
= VLOOKUP(value, range, col, FALSE)
= INDEX(range, MATCH(value, range, 0), col)
The second version is slightly slower, but... The slow operation is MATCH, only slightly faster than VLOOKUP, and the fast operation is the INDEX lookup. You notice that the formula in each column looks up the same ID from the same range, so you could insert a column with the row number returned by MATCH, and use that value in INDEX functions in all your columns. If you have 20 columns, this solution will be almost 20 times faster.
= MATCH([ID], MASTER[ID], 0) — new column “row”
= INDEX(MASTER, [row], 2)
= INDEX(MASTER, [row], 3)
You can be more subtle for the column number. The trick is to avoid repeating the same calculation.
Cheers!
(°v°)
= VLOOKUP(value, range, col, FALSE)
= INDEX(range, MATCH(value, range, 0), col)
= MATCH([ID], MASTER[ID], 0) — new column “row”
= INDEX(MASTER, [row], 2)
= INDEX(MASTER, [row], 3)
Cheers!
(°v°)
Microsoft Excel
--
Questions
--
Followers
Top Experts
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.