I have a spreadsheet with 3 columns, each with a list of names. Any name can be in multiple columns or just one column. I am trying to sort the spreadsheet so the same name in multiple columns are on the same row. It should look something like this
C1 C2 C3 Bob Bob Hillary John John John Mary Mary Ron Steve Steve Xavier
How can I do this? Thanks for your help.
Last edited by rkanter; 06-27-2022 at 03:27 PM.
Hi rkanter and welcome to the forum,
Here is how you can accomplish your task.
In Column 1 where you have a Blank cell, Add the name to the right including a "Blank" to the name.
This would make C2 = "HillaryBlank". Do the same in C1 for "RonBlank" and "SteveBlank".
Then sort by column C1.
Next search and replace using "*Blank" and replace it with nothing.
Hope this helps.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks Marvin. This is just a sample. The actual file has hundreds of lines and there aren't actually blank cells. The data comes in with each column individually sorted alphabetically.
The original file is a CSV that looks something like this.
C1, C2, C3
Bob, John, Steve
John, Bob, Hillary
Mary, Ron, John
Xavier, Steve, Mary
Hey rkanter,
I guess we need a better sample file to do your problem. I don't understand what result you want and/or why. Perhaps a better explanation of what your are trying to accomplish would help.
The original file is a CSV with three columns. each column is a list of names. The first column has about 300 names, the second about 250 and the third about 50. Any name could be in any of the three columns, 2 of three columns or all 3. (you can have a name in column 2 and/or 3 that is not in column 1)
I need spreadsheet where the names are sorted alphabetically across all three columns and that any name that is in more than one column appear on the same row. For privacy reasons I cannot provide the actual file.
Hi Rkanter,Originally Posted by rkanter
I've questions about your input.
I read there aren't blank cells, but later I read not every column has the same number of names.
How is that possible?
I read also "The data comes in with each column individually sorted alphabetically, but in the CSV-example you posted the names aren't in each individually column in alphabetically order .
I don't understand this.
Last edited by HansDouwe; 06-28-2022 at 07:50 PM.
I can't give you what the actual information is for but imagine that each column represents a class, C1 is English, C2 is Math and C3 is History and each row is a student. it this example Bob is in English and Math, Hilary is only in History, Steve is in Math and History, etc.
I need to be able to find a person and see what classes they are in. I have tried this with a Pivot Table which will show a 1 for the correct cells in the matrix, but the data requires a bit of manipulation to get it to the point where I can use a pivot table.
Hi Rkanter,
Unfortunately this does not answer my questions about the input yet.
It is important to know exactly which rules your input complies with before a solution can be made.
See my posted questions earlier today.
Can you tell us more about this?
Greetings,
Hans Douwe.
Last edited by HansDouwe; 06-28-2022 at 08:02 PM.
Hi Rkanter,
When the ultimate goal is to put the data in a pivot table,
it seems advisable to me to put the data in a table with 2 columns: Name and Class.
Order doesn't matter. Then a clear pivot table in desired order can be created.
See Attachment.
Please tell me if this make sense for you..
I don't know how to create a a meaningful PivotTable with your suggested table layout.
Hans Douwe.
Last edited by HansDouwe; 06-29-2022 at 01:28 AM. Reason: have sense --> make sense
The only issue is that requires a bit of manual preparation before I can use the pivot table. When the data comes in the number of rows and columns will vary each time and I was hoping for a more automated process.
Dear Rkanter,
In an automatic process you need to define and standirize your input.
If you don't I can't help you unfortunally.
Can you influence how the input is delivered?
Now the class is missing in your input and the rules are not clear.
See all my questions about this, none of which has been answered.
And did you see my last post with example?
Hans Douwe
Unfortunately, we have no control over the file that is provide. The only option available is to have each column sorted individually.
It is coming from an old system and this is the only data that is available.
I actually thought I answered your questions.
An example input is provided previously, as is what we need the result to look like.
Alternatively, the results can look like a pivot table with a 1 indicating a match for a person and class,but as pointed out that requires manipulating the data into two columns, one with name and one with class.
I actually did this manually for one set of data, but this is not doable with a large number of rows and columns.
I also need the process to be more automated given the users that will be running this.
Now I read also a large number of columns is possible. That is important new information about the input.
I'm sorry, without class in your input and clear rules about your input I can't help you futhermore.
Your example input is not complient with the input rules that you wrote and the input rules are ambigu.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks