Forum Discussion
how to sort rows based on content within column A
Hi, If someone can provide an easy solution I'd really appreciate it. I'm new to Excel, is there a way to do this? Thanks! David
This a screenshot of a file listing items (old vinyl records) sold, column A is title of listing, the following columns contain related sales data for each item. This is how the file comes to me. There are three formats of records sold mixed together in this list: LP records, 12" records, 45 records. Please notice each title in column A contains either an LP, 12" or 45. I am trying to separate these three types of records sold from each other, into groups. I am looking for the simples way to do it.
3 Replies
orivious hello there,
You could add a formula column, Table your data, then summarize with PivotTables to separate them out if you'd like. Or better yet, keep all the data in a single PivotTable and add a slicer by type. It would require an additional column to be added adjacent to your data, so I'm assuming in F1 copied down until the end of your data set.
=IF(ISNUMBER(FIND("LP",A1)),1,IF(ISNUMBER(FIND("12""",A1)),2,IF(ISNUMBER(FIND("45",A1)),3,0)))If you wanted to return text identifiers instead of numerical, you could change it to:
=IF(ISNUMBER(FIND("LP",A1)),"LP",IF(ISNUMBER(FIND("12""",A1)),"12""",IF(ISNUMBER(FIND("45",A1)),"45",0)))Table your data by selecting your data range, going to Insert > Create Table from Selection. Ensure you do not check 'my table has headers'. Then from the Table Design tab, click 'Summarize with PivotTable'. Drag 'Column 1' to the Rows area. If the other values are desired, add them to the Rows area to show values, or the Values area to aggregate them (i.e. SUM, AVERAGE, etc.). Finally, either Insert Slicer for 'Column 7' or add that field to the Filters area.
The simplest way to do it would be to go back to the source and insist they deliver it with the type of record as a column of its own.
That said, you'll need to use the various text manipulation functions, and that will be imperfect, but should process the bulk of them. Is it possible for you to upload and post the actual spreadsheet (unless it's just huge; in which case maybe 100 rows of it).
Since you're a beginner with Excel, it would be a lot easier for somebody else to write the formula for you, and it would be easier for me (or somebody else here) to write and test the formula on the actual sheet rather than an image.
- thank you! I think i can actually do this by filtering
