Jan 18 2020 01:26 PM
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
Jan 18 2020 01:58 PM
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.
Jan 18 2020 02:04 PM
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
Jan 18 2020 02:15 PM
@alisam1958 , in first column you have values like 102365 W 10 ? How when columns M and W appears in Pivot Table?
Jan 18 2020 03:01 PM
Jan 18 2020 10:46 PM
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
Jan 19 2020 02:32 PM
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