Excel Advance: Count Unique Items and Lists of Unique Items. কিভাবে অনেকগুলো আইটেমের একটা লিস্ট থেকে শুধুমাত্র একক আইটেম গননা করবেন এবং তা লিস্ট আকারে বের করবেন।

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

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

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

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

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

এখন যে সেলে আমরা একক আইটেমগুলো লিস্ট আকারে দেখতে চাই সেই সেলে নিচের 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)),"")}

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

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

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

যেকোন সমস্যা হলে কমেন্টে অথবা মেইলে জানাবেন। ইমেইল: harun24hr@gmail.com

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