Hi, I have a speadsheet with titles such as Name, date, telephone numbers, company name.... I have applied filter on the titles and sorted by company name. On the top of the spread sheet, I want to have a cell that shows the Compay name that I have sorted.(this cell is not static). What i mean not static is that I wish to have this Cell change automaticly everytime when I apply the sort for another Company Name so that I don't have to manually type the cell each time. Please kindly let me know if that is possible and how to do it. Thanks.
Filter and Sort on Excel
- Thread starter Tengatsu
- Start date
SirJB7
Excel Rōnin
Hi, Tengatsu!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
Besides, you might want to read this (2nd post, author venkat1926):
http://www.ozgrid.com/forum/showthread.php?t=154177
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
Besides, you might want to read this (2nd post, author venkat1926):
http://www.ozgrid.com/forum/showthread.php?t=154177
Regards!
Hi Luke, I finally tried it and for some reason, it only work for the first 2 colomn of the name, if i extend the helper column to more then it only shows the last company name. for example, compnay A (A6) at the helper colomn i typed "=subtotal(103,A6)", and Company B(A7) at helper colomn i typed "=subtotal(103,A7)", when i sort, it works, it shows the company name if i sort name Company A, or Company B, but now Company C (A8), when i type in the helper colomn "=subtotal(103,A8)" it actually only gives me company C name and any sort will still show Company C. Did i do something wrong? Thanks
I think I’m getting confused with the terms 'sort' and 'filter' being used somewhat interchangeably.
So you have a list of date containing: Name, Date, Phone #’s, Company Name, … you’ve sorted this list by Company name, is this the first column? When you do this are you just seeing the data for that specific Company? If so this is technically called filtering. (When you sort you rearrange all data. So you’d see all companies’ data, just in a specific order. When you filter, you only see specific data. Filter for Company B and you’ll only see Company B data. Change the filter for Company E and you only see Company E’s data).
If I’m still following you correctly… lets say that your titles are in A4:F4 (or whatever the last column would be) and your first ‘record’ would then be in A5. If column A contains your company names, you can place the following in whatever Cell at the top of your document: “=A5”. If the Company Name field is not the first one, it may be a nice time to play with PivotTables to easily rearrange how the data is viewed, grouped, filtered, etc…
Hope this helps… if not…. I blame Luke.
So you have a list of date containing: Name, Date, Phone #’s, Company Name, … you’ve sorted this list by Company name, is this the first column? When you do this are you just seeing the data for that specific Company? If so this is technically called filtering. (When you sort you rearrange all data. So you’d see all companies’ data, just in a specific order. When you filter, you only see specific data. Filter for Company B and you’ll only see Company B data. Change the filter for Company E and you only see Company E’s data).
If I’m still following you correctly… lets say that your titles are in A4:F4 (or whatever the last column would be) and your first ‘record’ would then be in A5. If column A contains your company names, you can place the following in whatever Cell at the top of your document: “=A5”. If the Company Name field is not the first one, it may be a nice time to play with PivotTables to easily rearrange how the data is viewed, grouped, filtered, etc…
Hope this helps… if not…. I blame Luke.
So quick to throw me under the bus jason? =P
Here's the data I was using, in range A2:D8:
[pre]
[/pre]
note that the helper columns are separate columns. formula in C3:
=SUBTOTAL(103,A3)
This is copied 1 to the right and down to row 8.
In A1, formula is:
=LOOKUP(1,C3:C8,A3:A8)
This can be copied to the right to use col B and helper2 columns.
Overall though, I'm not sure I understand why this is needed. If I filter for company c, that's what I'll see down below. Why would we need another cell stating this?
Here's the data I was using, in range A2:D8:
[pre]
Code:
Comp Score Help1 Help2
a 1 1 1
a 2 1 1
b 3 1 1
b 1 1 1
c 2 1 1
c 3 1 1note that the helper columns are separate columns. formula in C3:
=SUBTOTAL(103,A3)
This is copied 1 to the right and down to row 8.
In A1, formula is:
=LOOKUP(1,C3:C8,A3:A8)
This can be copied to the right to use col B and helper2 columns.
Overall though, I'm not sure I understand why this is needed. If I filter for company c, that's what I'll see down below. Why would we need another cell stating this?
Hi Jason,
Sorry Filter should be the right word. I actually tried Luke's way and it sort of partially worked. To clearify abit more, under the company name, ie, Company A, B,C,... through out the table there might be more than one Company A. After I selected the Filter for Company A, there will be a few roll with Company A. With the function that Luke showed me (Which is really great that i learned something new), it will work only if there are only 1 Company A in the list, with multiple the function does not work. Is there a way that will be able to make it work? Thanks.
Sorry Filter should be the right word. I actually tried Luke's way and it sort of partially worked. To clearify abit more, under the company name, ie, Company A, B,C,... through out the table there might be more than one Company A. After I selected the Filter for Company A, there will be a few roll with Company A. With the function that Luke showed me (Which is really great that i learned something new), it will work only if there are only 1 Company A in the list, with multiple the function does not work. Is there a way that will be able to make it work? Thanks.