# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  > [SOLVED] Using a Pivot to put all cells with matching reference number on same row

## DanEveritt

Hi guys,

I have a sheet with a column of reference numbers and a column of data associated with the reference numbers. As each reference number can have multiple pieces of data the references appear in column A multiple times. Example:


A
B

1
Reference Number
Data

2
12345
Data1

3
13245
Data2

4
ABCDE
Data3

5
ABCDE
Data4

6
ABCDE
Data5



I am looking for a for way to perhaps have a pivot table that can have a each row represent each unique reference number and then have each cell on that same row have each data in it:

12345
Data1
Data2


ABCDE
Data3
Data4
Data5



The idea is that I can then do a simple text join at the end of each row to have a combined data cell for each unique reference.

What settings to do i need to set on the pivot table so i can achieve this? Ive played about with it a fair bit and I cant work it out.

Thanks in advance!

Dan

----------


## Richard Buttrey

Hi,

You could make use of the little known ConatenateX in the DAX set of functions in the Data Model associated with Pivot Tables 

See attached.

Here's a link to an excellent explanation

https://sfmagazine.com/post-entry/ju...a-pivot-table/

----------


## DanEveritt

Hi Richard!

Thanks! I will give it a thorough read.

Your time is much appreciated.

----------

