How To Show The Values On Pivot Table

Data is on Sheet1.

I tried to create column names from the cell values on the column A with pivot table as you see on Sheet2.

Like; 1-2, 2-3, 3-4, 4-5, ...

And I want the values on the column B are shown below those column names.

But I didn't make it work. It shows the values as count, sum, max, min. etc. (on Pivot Table)

I want just values.

I also tried Copy-Paste, Paste Special & Transpose but then the column names are not merged. For example there are several 1-2 columns.

How to do that?

Note : Pivot Table is not necessary.

Here's what I did.
First, I got the unique values from Col 1 (Remove Duplicates) and transposed them into row 1.
Then in D2 copied across and down

Formula:

Please Login or Register  to view this content.

See attached sheet. Is that what you wanted?

PS. I don't think it's possible with a pivot table.

When I copy paste your formula It always shows the same values. (Sheet3)

???


Edit : While copying and pasting the cell D1 does not change for some reason. But when I apply the fill to the formula It works.

Thank you very much. It works.

If you use COUNTA on a range that contains formulas, it counts all cells that contain formulas. If you manually count the numbers in the extracted range, you get 51.
If you need to count them try

Formula:

Please Login or Register  to view this content.

As far as I understand, I can suggest the following code but first; column headers in Sheet1 should be changed as Column1 and Column2

After the column headers are updated manually as stated above, you can run the below macro;

Please Login or Register  to view this content.

Sample file is attached...