# sorting 1 table of mixed results in to 2 separate tables

1. ## sorting 1 table of mixed results in to 2 separate tables

excel.JPG
ok so I have a table of codes (combination of numbers and letters) that either end with "-a" or "-b" and each code has a value (numerical).
So I have column A that is the codes and column B that is the value, but i want to sort them into 2 separate tables one table for the codes ending "-a" and one table for the codes ending "-b" but I'm Lost as to how to do this. ultimately the 2 separate "sorted" tables will be on separate spreadsheets accessible by 2 separate groups of people. The original table will change on a regular basis but the codes will still have the"-a" or "-b" identifier.
any help would be greatly appreciated

ps I have basic understanding of excel but little knowledge of the more complex formulas

2. ## Re: sorting 1 table of mixed results in to 2 separate tables

Insert a new column A and enter this formula and copy down. Then filter on the A values and copy into the table for A. Repeat for B values. You could also just sort on the new column A so that all A values would be together and the B values would be together. Select each of the rows with the values and copy and paste.

Formula:
`Please Login or Register  to view this content.`

3. ## Re: sorting 1 table of mixed results in to 2 separate tables

thanks for the reply not quite the solution I'm looking for though I suspect I'm over thinking the situation basically I have a-grade and b-grade products. A-Grade products are 70% of the retail price and b-grade products are 60% of the retail price. my original thought was separating my list in to "-a list" and "-b list" then transferring the data in to a price label template that would work out the price. basically whatever system is used needs to be idiot proof, I can't rely on my staff to use filters etc they just need to be able to copy and paste the information into a table (the product list changes twice daily) and print the labels (no filtering or working out involved like I said idiot proof)
I was toying with the below example of having a formula in the now price box that would set the price based on whether the product code finished in "-a" or not. if it ends "-a" then price is set at 70% of retail if it doesn't finish in "-a" then by process of elimination it must be a b-grade item (-b) and price would be set at 60% but I'm getting myself stuck
excel2.JPG

4. ## Re: sorting 1 table of mixed results in to 2 separate tables

the formula that I hashed together works fine with "-a" but not if its "-b" on the end and thats what I'm struggling with

5. ## Re: sorting 1 table of mixed results in to 2 separate tables

See attached file.

Not sure what you are after 100% but does is that alogn the right lines?

6. ## Re: sorting 1 table of mixed results in to 2 separate tables

It does not appear to be difficult to separate the data but what do you do with the duplicates in the list? If you are keeping the duplicates, how do you determine what value is relevant?

Is new data just appended to the bottom of old data or does it replace the old data?

A workbook with realistic data would be good to have. Please upload one if at all possible.

7. ## Re: sorting 1 table of mixed results in to 2 separate tables

You need to create a separate index for each code - see columns a and b of the attached spreadsheet
Then use vlookup to select what you want in columns f,g,i and j

Only requires use of the MAX and VLOOKUP functions, MAX is simple, and VLOOKUP is a very useful one to learn, it has many purposes

There are currently 1 users browsing this thread. (0 members and 1 guests)