Limit Sorting to Multiple Sets of Rows in Same Column

Hi, I've try to search for this every where but got no luck. I'm trying to create some materials for studying. I'm looking for any solutions (macro or add-ins, even paid) that could do a multiple sorting but limit the sorting within a range.

For example, in the attached spreadsheet, I would like to be able to sort automatically the whole Column C, but the sorting are restricted to each category. That is, "aa,bb,cc,dd" will only be randomized within Category 1 box, c2:c5. And, "kk,ll,mm,nn"would only be randomized within c6:c9, etc. The number of rows will be static at 4 cells/rows per category.

Is this even possible? I can do them manually but I will have hundreds more. I tried to create macro but the macro only sort what I selected. This means I have to record everything first then reuse the macro next time I need to randomize Column C.

-I'm thinking maybe there's a macro that can sort every 4 rows (column C & D for randomization), until the end of the column (no more text).

-I think my situation might be some what similar to this case: http://www.excelforum.com/excel-prog...gle-sheet.html

However, I have no idea what any of those code really means.

Thank you for your generous help.

Originally Posted by kvsrinivasamurthy

Is it always 4 rows.

Hi, kvsrinivasamurth. Yes, it is.

Originally Posted by leelnich

Hi all. Paste this in a standard module. Starting on row 2, it sorts columns C:D of the active sheet in 4-row sections:

Please Login or Register  to view this content.

hi, neelnich. Wow, that works magically!

Hope you don't mind, but on the same sheet, is there quick/automated way to copy/paste/drag the formula to be incremental every nth row?

For example:

B2: =Sheet1!B3&CHAR(10)
B6: =Sheet1!B4&CHAR(10)
B10 = Sheet1!B5&CHAR(10)

on an don...?

Thanks, again!

wow, you're amazing. Thanks. I'd buy your a beer if there was a donate button.

Thanks so much!

Hi, leelnich,

I'm using your code and came up with another similar to the formula you gave me.

It is possible to do offset for column?

For example: I want to fill:

Data!E3
Data!F3
Data!G3
Data!H3
Data!E4
Data!F4
Data!G4
Data!H4


And that it would fill down the same pattern every 4 rows?

Thanks, again.

Oh, sorry.

I attached the spreadssheet. So pretty much, I just want to be able to fill the rest of the categories on Sheet1 to match the pattern:
A#
B#
C#
D#
A#
B#
C#
D#

Should I make a new thread or this is ok?

Thanks, again.

Ah, I was trying to do with TRUNC(COLUMNS). Thanks, again!