Automatically select ranges and apply Concatenate Unique

[Peter Mooney]

I have a table of 100,000 plus rows containing finance related data in which I would like to concatenate all the AccountTypes associated with a specific Journal Entry Identifier (JEIdentifier). Column 1 gives the JEIdentifier, Column 2 the AccountTypes, Column3 shows the AccountTypes prefixed with "c" for Credit and "d" for debit. I have found a function on this forum which allows you to specify manually a specific range of data to evaluate a set of records but what I would really like is code that will loop through the JEIdentifier Column and for each JEIndentifier return the associated AccountTypes. I imagine the records could be sorted by JEIdentifier so the code can work through sequential blocks of rows instead of having to read the entire ordered data set of 100,000 rows.

JEIdentifier	AccountType	AccountType2
1000101994	Liabilities	c|Liabilities	c|Liabilities, d|Liabilities, c|Assets, d|Assets
1000101994	Liabilities	d|Liabilities
1000102019	Assets	c|Assets
1000102019	Assets	d|Assets
1000102154	Liabilities	c|Liabilities
1000102154	Liabilities	d|Liabilities
1000102155	Liabilities	c|Liabilities
1000102155	Liabilities	d|Liabilities
1000102156	Liabilities	c|Liabilities
1000102156	Liabilities	d|Liabilities
1000102157	Liabilities	d|Liabilities
1000102157	Liabilities	c|Liabilities
1000102158	Liabilities	c|Liabilities
1000102158	Liabilities	d|Liabilities
1000102159	Liabilities	c|Liabilities
1000102159	Liabilities	d|Liabilities
1000102160	Liabilities	c|Liabilities
1000102160	Liabilities	d|Liabilities
1000102160	Liabilities	d|Liabilities
1000102163	Liabilities	c|Liabilities
1000102163	Liabilities	d|Liabilities
1000102163	Liabilities	d|Liabilities
1000102225	Assets	c|Assets
1000102225	Assets	d|Assets

<colgroup><col width="92" style="width:69pt"> <col width="111" style="width:83pt"> <col width="140" style="width:105pt"> <col width="316" style="width:237pt"> </colgroup><tbody>
</tbody>

Function ConcatUniq(xRG As Range, XChar As String) As String
'updateby Extendoffice 20151228
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRG
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, XChar)
Set xDic = Nothing
End Function

 

[Peter Mooney]

Hi helpexce, I tried this formula in cell D2 where it returned a value of 1 in D2 and displayed the formula in cell E2. Please see Akuini's solutions version b and d. Thanks

 

[Akuini]

Hi Akuini, many thanks for your solutions. Version b returns concatenated unique values in column C associated with unique values in column A; and version d returns concatenated unique values in column C associated with each value (row) in column A. Perfect solutions thank you and super fast to execute for 240,000 rows on an intel i7, 16GB Ram laptop.

You're welcome & thanks for replying