Sort & Group Specific Cell Value

spartacruz

New Member
Joined
Jun 22, 2021
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi All,

I would like to know how to achieve this condition programmatically.
The main idea is to collect the same "attribute name" for each row for each grouping (column 2)
Note: Grouping will be more than 1. like : Logi1, Logi2, Logi3, etc

From this :
1624337236398.png


To be like this (result):
1624337261562.png


I had imagined the algorithm of script:
1. Count attribution/row of each row. The longest attribute (in 1 grouping) will be the main comparison. >> I could make it
2. Filter based on PG name and sort desc its count (So, the first line of each PG will be the longest attrib) >> I could make it
3. Then the difficult part : switch/copy-paste each attribution as an example >> it's very hard for me. Because i don't really know how to achieve it.

Could someone assist me to solve this (number 3) in smarter way?
Any suggestion will be appreciate.

Thanks.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,689
I looked at this earlier, and so did 46 other people. I suspect that you didn't get any replies because the question is not very clear, even with a before/after example and explanation. I ended up ignoring your explanation altogether and looked at the example. Based on that, I came up with these rules:

1) Sort each row by the column count
2) On the top row, sort each flag/name/value combo by name.
3) On each subsequent row, align the attributes with the row above it. Add any attribute names unused so far to the right.

Maybe on point 3, you'd prefer to get a list of all attribute names for the whole table, and put them in sorted order, so that you don't have CCCC after QQQQ?

How many rows are in your table? How many columns (or attributes) could you have? Do you want the table changed in place, or written somewhere else?

Let me know if that's what you want. If I have time, I'll try to look at this again.
 

spartacruz

New Member
Joined
Jun 22, 2021
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Thank you for your reply. Really appreciate it.

I looked at this earlier, and so did 46 other people. I suspect that you didn't get any replies because the question is not very clear, even with a before/after example and explanation. I ended up ignoring your explanation altogether and looked at the example. Based on that, I came up with these rules:

1) Sort each row by the column count
2) On the top row, sort each flag/name/value combo by name.
3) On each subsequent row, align the attributes with the row above it. Add any attribute names unused so far to the right.

Maybe on point 3, you'd prefer to get a list of all attribute names for the whole table, and put them in sorted order, so that you don't have CCCC after QQQQ?

How many rows are in your table? How many columns (or attributes) could you have? Do you want the table changed in place, or written somewhere else?

Let me know if that's what you want. If I have time, I'll try to look at this again.

Oh really? I am sorry if I was not explain it clearly.

Yes, your understanding are correct enough. I just wanna add some detail.

1) Sort each row by the column count
Correct. By the way, this column generated by vba (created by me) count all flag/name/value.

Each row represent individual item of "NO" (Column 1. imagine its like ID on database, have to be unique) and belong to "Grouping Name" (Column 2).
As i said before, "Grouping Name" will be more than 1. like : Logi1, Logi2, Logi3, etc

For example :
Say Logi1 have 100 "NO" / rows (1 - 100)
Logi2 have only 5 "NO" / rows (101 - 106)
Logi3 have 2000 "NO"/rows (107 - 2107)

Auto filter for each distinct value of "Grouping Name" (column 2) -> After filter applies -> sort descending "Column Count" (column 3).

'Sort descending' only applies for each individual filter of "Grouping Name" only, not the entire rows.

So, every first row of Logi* :
first row of logi1 is row 1,
first row of logi2 is row 101,
first row of logi3 is row 107,

must be the longest attribute and all attribute name under the longest attribute have to follow it.
Because the longest attribute will be the main comparison for each "Grouping Name" to stack their attribute name.

2) On the top row, sort each flag/name/value combo by name.
3) On each subsequent row, align the attributes with the row above it. Add any attribute names unused so far to the right.

Actually, this process have to be combine with my statement before.

"Grouping Name" filter still applies -> after sort descending -> for each row on selected "Grouping Name", sort ascending flag/name/value based on attribute name vertically.

Next -> rows under 'the longest attribute' have to follow it based on name.
For example, on Logi1

Attrib name 1 column is "AAAA" then all column have to be "AAAA" only
Attrib name B column is "BBBB" then all column have to be "BBBB" only
etc...

Maybe on point 3, you'd prefer to get a list of all attribute names for the whole table, and put them in sorted order, so that you don't have CCCC after QQQQ?

This happen because : The longest attribute of that "Grouping Name" does not have attribute "CCCC"

As i said, all attribute have to follow their 'the longest attribute' of each "Grouping Name".
So, if certain 'attribute name' does not contain on 'the longest attribute', then it must be placed on the last order after all attribute. That's why CCCC placed there.

How many rows are in your table?
Could be 100k row. on average are 20 - 50k row

How many columns (or attributes) could you have?
26 (twenty six) attributes/column

Do you want the table changed in place, or written somewhere else?
Written on another sheet (like 'result') will be nice.

My current trial is changed in place. But i wonder if written on another sheet so i still have a backup data.


------

Hope my explanation could be understand easily.
Please have some comment if you confuse or something.
 

Forum statistics

Threads
1,148,370
Messages
5,746,299
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top