Automatically select ranges and apply Concatenate Unique

P

Peter Mooney

New Member
Joined
Jul 29, 2017
Messages
6
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.

[TABLE="width: 658"]
<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>[TR]
[TD="width: 92, bgcolor: transparent"]JEIdentifier[/TD]
[TD="width: 111, bgcolor: transparent"]AccountType[/TD]
[TD="width: 140, bgcolor: transparent"]AccountType2[/TD]
[TD="width: 316, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000101994[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities, d|Liabilities, c|Assets, d|Assets[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000101994[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102019[/TD]
[TD="bgcolor: transparent"]Assets[/TD]
[TD="bgcolor: transparent"] c|Assets[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102019[/TD]
[TD="bgcolor: transparent"]Assets[/TD]
[TD="bgcolor: transparent"] d|Assets[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102154[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102154[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102155[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102155[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102156[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102156[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102157[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102157[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102158[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102158[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102159[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102159[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102160[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102160[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102160[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102163[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] c|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102163[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102163[/TD]
[TD="bgcolor: transparent"]Liabilities[/TD]
[TD="bgcolor: transparent"] d|Liabilities[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102225[/TD]
[TD="bgcolor: transparent"]Assets[/TD]
[TD="bgcolor: transparent"] c|Assets[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000102225[/TD]
[TD="bgcolor: transparent"]Assets[/TD]
[TD="bgcolor: transparent"] d|Assets[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Peter Mooney said:
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.
Click to expand...


You're welcome & thanks for replying
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Concatenate Unique Values - Minor VBA issue
Replies
4
Views
2K
Dan5977
D
Q
  • Question Question
Formula to Concatenate Unique Values into 1 cell
Replies
3
Views
954
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top