Auto-Sort or Auto-Filter

Hello,

Here is the sample file for the question ---> List Example.xlsx

In the attached sheet you will see data in 2 columns in Sheet 1. Column A has similar numbers and column B has random numbers. Is there any way to create a filter or pivot table where all values of column B are listed with the same column number?

For example. The first 3 rows that contain "1" in column A have a column B value of 0.88%, 1.09%, 1.23% ( I have highlighted them yellow)

In sheet 2 you will see list "1" and list "2". I did this by filtering column A, first by "1", then by "2", and pasting the values from column B each time. You will see the highlights appear at the top of "1". Is there any way to automate this for each time I paste a new set in Sheet 1. This would list all number utilized in column A. Which would be 1-31

The end result is that I would like to put a formula on each data set in the filtered results.

I hope I explained this correctly.

Thanks in advance for any help!!!!

dont think you can do it with just sort/pivot table or filter

you can either do it like the attached whereby you have headers 1-31 and CSE formula to fill in the rest

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Or you go can have some macro to do it for you