Excel Lists

Copper Contributor

I am looking to take a worksheet of various names and copy/sort those names to 5 other sheets, i.e. all of the names Bill appear on the same worksheet, all the names Mike, appear on the same worksheet, etc. How can I do this?

8 Replies

@MarkK2615 

 

The "this" that you are wanting help to accomplish is not at all clear. Why don't you start with a far more clear description of the raw data and the desired outcome. 

 

What's the purpose of the sort to the 5 different sheets in the first place? It's not clear, for example, whether you WANT all of the names of "Bill" to appear on the sam worksheet or if that's what you're hoping to overcome. 

I have a construction company. I have a worksheet that lists my 53 projects. Each project/listing on that worksheet has a Project Manager name associated with it. Any one Project Manager may have five or six projects to handle. So as I look through that list of 53 projects, I see the names randomly. I would like to take each of those names/entries and have them also show on another worksheet/"summary" worksheet, thus a worksheet with all Bill's jobs, a worksheet with all Dan's jobs, a worksheet with all of Mike's jobs, etc. I cannot do an = formula to have what is in one cell show up in another, because project locations move within the lines of the worksheet, closed projects go away, new projects are added. Thinking of an IF formula; if a range of cells has the name Mike in it, then the info will show in the cell within the "summary" worksheet.    Sorry, does this explain it better?

@MarkK2615 

Attached is a sample workbook. This method uses the FILTER function.

Thank you Patrick. I will have to figure out how to open this. My work firewall filtered it. I can't open it.

@MarkK2615 

 

Here are two good sources for understanding the FILTER function. So if you can't open @Patrick2788 's example, perhaps you could create your own.

https://exceljet.net/excel-functions/excel-filter-function

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...

@Patrick2788 

I am not sure I understand "I cannot do an = formula to have what is in one cell show up in another, because project locations move within the lines of the worksheet, closed projects go away, new projects are added".  Possibly, a statement that more than a simple relative reference is required.

If the initial "ProjectList" were an Excel Table then it grows and reduces with the data it contains and reference to entire fields can be made as structured reference.  I also did the filtering by referring to the tab Name.

What was cute was the way

= LET(
    name,           TEXTAFTER(CELL("filename",A1),"]"),
    filteredByName, FILTER(ProjectList,ProjectList[Project Manager]=name),
    DROP(filteredByName,,1)
  )

works for each name, including newly defined ones.  TEXTAFTER and DROP are going to be an issue for a while though.

@Peter Bartholomew 

TEXTAFTER and DROP is temporary issue, but CELL("filename") is more serious one since it doesn't work in Excel for web. More and more often, especially in corporate environment, people use both. Possible scenario is to open in browser to check something or do small changes and from it, if necessary, open in desktop app for more heavy tasks.

@Sergei Baklan 

Good point. I accept that.  There was a time when most of my workbooks used VBA event handlers (MouseMove etc) to animate diagrams built using 3D shapes.  I came to think of the online version of Excel as having all the utility of a chocolate teapot, so tended to neglect it.  Much has changed since then.

 

 

Lens.PNG