Sorting Data From Multiple Columns

[Quantumplating]

Hello All,

I have challenge for you. Below I have a simple table (This is actually in a table so all of the columns are able to be sorted by row) with a series of columns and a hodge podge of made up data. The first two columns are raw data that I will manually be entering, and I want to be able to sort those columns using the table sorting. I have a few helper columns that then take the data and sort it into the sorted data column. That means regardless of the order of the data in the first columns the sorted data will always be sorted. I have that working after scrutinizing the web for a while.

Here are the formulas for the helper columns:

Helper 1 =COUNTIF([Raw Data],"<="&I4)
Helper 2 =--ISNUMBER(I4)
Helper 3 =--ISBLANK(I4)
Helper 4 =IF(ISNUMBER(I4),K4,IF(ISBLANK(I4),K4,K4+$L$1))+$M$1

And here is the formula for the sorting cell:

Sorted Data =IFERROR(INDEX([Raw Data],MATCH(SMALL([Helper 4],ROWS($O$4:[@[Sorted Data]])+$M$1),[Helper 4],0)),"")

I want the data in the column next to it (currently called column1) to be permanently sorted based on the sorted data in the sorted data column. For example US has a two next to all of the entries but it should have a 98798 next to one of the 4 duplicate entries in column 1. Notice too that power and exit are not next to each other in the sorted data. What is the formula to sort column1 1 based on the sorted data column?

[TABLE="width: 647"]
<tbody>[TR]
[TD]Raw Data[/TD]
[TD]Raw Data 2[/TD]
[TD]Helper 1[/TD]
[TD]Helper 2[/TD]
[TD]Helper 3[/TD]
[TD]Helper 4[/TD]
[TD]Sorted Data[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]234[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]444[/TD]
[TD]Cow[/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]Cheese[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]FAIR[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]Chicken[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]Chicken[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]UAE[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]FAIR[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]India[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]India[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]Love[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Power[/TD]
[TD]Exit[/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Sickness[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]88[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]Snars[/TD]
[TD]Wheat[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]98798[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]Spain[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cow[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]UAE[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]Exit[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]US[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Power[/TD]
[TD]Wheat[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]US[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Snars[/TD]
[TD]Wisper[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]14[/TD]
[TD]US[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Waffles[/TD]
[TD][/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]21[/TD]
[TD]US[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sickness[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]Waffles[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Love[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I appreciate the help.

Sincerely,

Quantumplating

 

[JLGWhiz]

If the data across the eight columns is relational, then only one column can comprise the absolute logical sort. If you try to sort another column to group all like items in that different column, it will disrupt the logical sequence of the primary sort column. Or if you only sort the second column to group the items, then the data in the other seven columns becomes invalid. However, if none of the data in the seven columns is relational to any of the data in any of the other columns, then you can simply sort each column individually.

Or maybe I don't understand the question.

 

[Quantumplating]

Perhaps a little bit of clarification will help, and I can show where I have made it to. I am trying to make a meal planner, and I want to be able to pick a meal from a drop down list. Once I have selected all of the meals I want to go to another tab and print a complete grocery list organized by what type of food it is. For example all of the produce will be together in the grocery list. On the first worksheet there is a calendar with several drop-downs that list all of the recipes I have. Then the next page has a list of the ingredients required per recipe.

Basically I want the output to be a grocery list that will always have the type of food clumped together regardless of how the data are sorted in the other tables, as they will likely be sort by alphabetical order of the recipe name or the food name.

This is the progress I have made so far:

=IFERROR(OFFSET(INDEX([Raw Data],MATCH(SMALL([Helper 4],ROWS($P$4:[@Column1])+$M$1),[Helper 4],0)),0,1),"")

This is the function in column1. If the right number is put in the offset function for the row location this function will work perfectly. No matter what the order is of the raw data, the data in raw data 2 will be offset to the right place.

Let x1 be the location in the table of the first duplicate value in the column and x2, x3, ..., xn be the locations of the subsequent duplicate values. The offset value only has to be xn - x1.

If I can find a function that yields that row information this whole thing will work.

For the data that I posted above that column of data would look like this:

0 (0's are values that are either the first of a duplicate value, or they are a unique item)
1 (Any value greater than 0 shows the offset necessary to grab the correct information to correspond to a previous duplicate value)
2
0
0
0
0
0
0
0
0
0
12 (For example the first US cell was 12 cells above this one)
4 (For example the first blank cell was 4 cells above this one)
0
0
0
0
0
0
9

If anyone can find this function the problem will be solved.

Sincerely,

Quantumplating

 

[JLGWhiz]

Thanks for the feedback. I now understand your objective but unfortunately, my expertise with formulas is very limited and not sufficient to help in this case. Maybe someone with more skill in formulas will assist you.
Regards, JLG