টিউনের শিরোনাম দেখে অনেকের কাছে এলোমেলো মনে হতে পারে। আমি আজকে দেখাব কিভাবে অনেকগুলো আইটেমের একটা লিস্ট থেকে শুধুমাত্র একক আইটেম গননা  এবং তা লিস্ট আকারে বের করা যায়। ধরুন এক্সেলের একটি কলামে অনেকগুলো ডাটা এন্ট্রি করা আছে এবং একই আইটেম একাধিক বার আছে। এখন আমারা চাই ঐ কলামে মোট কতটা একক আইটেম আছে, এবং ঐ একক আইটেমগুলোর লিস্ট। যেকোন সময় নতুন আইটেম যদি এন্ট্রি করা হয়, তাহলে যেন অটোমেটিক ঐ আটইটেম একক আইটেমের লিস্টে চলে আসে। নিচের উদাহরনটি দেখলে বিষয়টি আরো পরিস্কার হয়ে যাবে।

এখানে একটা sample sales data এবং report দেখানো হয়েছে। Report থেকে আমরা দেখতে পাই মোট কতটি পন্য এবং তাদের লিস্ট এবং কোন পন্য কত পরিমান সেল করা হয়েছে তার পরিমান। এই কাজগুলোই এক্সেলে formula এর মাধ্যমে কিভাবে বের করতে হয় তা দেখাব। বুঝার সুবিধার জন্য sample file download করুন।

Sample File Download: Count And List Unique Items    Zip File.

(1) প্রথমে একটি এক্সেল ফাইল নিয়ে তাতে উপরের চিত্রের মত ডাটা এন্ট্রি করি। এরপর যে সেলে আমরা একক আইটেমগুলোর গননার ফলাফল দেখতে চাই সেই সেলে নিচের formula বসান।

=SUMPRODUCT((D3:D1000<>"")/COUNTIF(D3:D1000,D3:D1000&""))

(2) এখন যে সেলে আমরা একক আইটেমগুলো লিস্ট আকারে দেখতে চাই সেই সেলে নিচের formula বসান।

=IFERROR(INDEX($D$3:$D$25,MATCH(0,COUNTIF($I$3:I4,$D$3:$D$25),0)),"")

এটি একটি অ্যারে formula তাই formula বসানোর পর অবশ্যই Ctrl+Shift+Enter চাপবেন, তাছাড়া formula টি ঠিক মত কাজ করবে না। Ctrl+Shift+Enter চাপার পর formula টি এরকম দেখাবে:

{=IFERROR(INDEX($D$3:$D$25,MATCH(0,COUNTIF($I$3:I4,$D$3:$D$25),0)),"")}

(3) প্রতিটা আইটেম কতটা সেল হয়েছে তা বের করার জন্য এই ফরমুলা ব্যবহার করুন।

=IF(I4="","",SUMIF($D$3:$D$25,I4,$E$3:$E$25))

যারা vba use করতে পারেন তারা নিচের Custom Formula ব্যবহার করতে পারেন। Zip ফাইলে সবকিছু দেওয়া আছে।

যেকোন সমস্যা হলে টিউমেন্টে অথবা মেইলে জানাবেন। ইমেইল: [email protected]

Function CountUnique(ByVal MyRange As Range) As Integer

Dim Cell As Range

Dim J As Integer

Dim iNumCells As Integer

Dim iUVals As Integer

Dim sUCells() As String

iNumCells = MyRange.Count

ReDim sUCells(iNumCells) As String

iUVals = 0

For Each Cell In MyRange

If Cell.Text > "" Then

For J = 1 To iUVals

If sUCells(J) = Cell.Text Then

Exit For

End If

Next J

If J > iUVals Then

iUVals = iUVals + 1

sUCells(iUVals) = Cell.Text

End If

End If

Next Cell

CountUnique = iUVals

End Function

Function listUnique(rng As Range) As Variant

Dim row As Range

Dim elements() As String

Dim elementSize As Integer

Dim newElement As Boolean

Dim i As Integer

Dim distance As Integer

Dim result As String

elementSize = 0

newElement = True

For Each row In rng.Rows

If row.Value <> "" Then

newElement = True

For i = 1 To elementSize Step 1

If elements(i - 1) = row.Value Then

newElement = False

End If

Next i

If newElement Then

elementSize = elementSize + 1

ReDim Preserve elements(elementSize - 1)

elements(elementSize - 1) = row.Value

End If

End If

Next

distance = Range(Application.Caller.Address).row - rng.row

If distance < elementSize Then

result = elements(distance)

listUnique = result

Else

listUnique = ""

End If

End Function