sorting mixed data in pivot table

[alisam1958]

hello gurus

I need some help with a pivot table.

I have a column A that has mixed values: meaning, some numbers, a W for women or M for man and additional numbers. Example, 102365 W 10

other columns contain other info, such as quantity.

I made 2 different columns separating the genders and now the result is mixed values in column A

What I would like is to group all the items with W and all the items with M, in column A.

How can I do that?

thanks in advance

[#2]

@alisam1958 

As variant, if you have in your source data the column with male/female you may group PivotTable on this data adding the field on the top of ones which are in Rows.

[#3]

@Sergei Baklan 

the only data about male female I have, is in the first column, where the letter W or M appear.

there are the next 2 columns that sort the quantity according to gender but the result, as you can see in the screenshot, mixes the 2 while what I need is dividing the two. Meaning, in the column A I would like to have all the items with M first and W after, with the quantities sorted accordingly

[#4]

@alisam1958 , in first column you have values like 102365 W 10 ? How when columns M and W appears in Pivot Table?

[#5]

The columns were there when i created the pivor. I just noticed the screenshot is partial and it looks like it is sorted but below, it is mixed. Columns W and M appear but the result is mixed: first few row show W, then M, then W agai, then Magain. I want them in 2 groups

[#6]

@alisam1958 

I mean something like this

[#7]

@Sergei Baklan 

thank you for your reply.

I am also curious to know how to do it this way but I need it rather like in the attachment

[#8]

@alisam1958 

That's slightly depends on your version of Excel. Creating the Pivot Table add data to data model and when in Power Pivot select sorting Item by Gender

If unsorted Pivot Table is like

after applying above Sort By it'll be shown as