Excel Advance: Filtering data automatically based on condition using functions.

Sample file download: Filter_Data_Based_On_Condition      Zip File (10 KB)

How to filter data using function in Excel. In above screenshot there are some data in left table. In right table filtered data are shown.

There are some tasks list and different task is in different status. We want to filter tasks those are already complete. Whenever a task is mark as complete it will automatically appear in filtered list. To achieve these filtering use following formula.

=IFERROR(INDEX($B$4:$E$18,SMALL(IF($E$4:$E$18="Complete",ROW($E$4:$E$18)-ROW($B$3),""),ROWS($B$4:$B4)),COLUMNS($B$4:B$4)),"")

*** Note: This is an array formula. So, must press Ctrl+Shift+Enter after typing the formula.

As above screenshot put the formula in G4 cell and fill the formula right and down.

Read More

MS Access: কিভাবে Checkbox ও Option Button ব্যবহার করবেন।

Sample File & Video Download: Checkbox_Optionbutton_Program   Zip File (44 KB)

Video Link: Checkbox_Optionbutton_Program

*** নিচের কোন লাইন না বুঝতে পারলে ভিডিওটা ভাল করে দেখবেন।

(1)    প্রথমে একটি নতুন অ্যাকসেস ফাইলে একটি নতুন Form নেই।

(2)    ফরমে তিনটি টেক্সট বক্স, একটি Option Group এবং তাতে তিনটি Option button ও তিনটি Checkbox নেই। টেক্সট বক্স তিনটির নাম দেই যথাক্রমে txtRed, txtGreen, txtBlue. Checkbox গুলোর নাম দে্ই যথাক্রমে chkRed, chkGreen, chkBlue.

(3)    এখন Option Group এর Click Event এ নিচের কোডসমূহ লিখি।

Private Sub FrameColor_Click()

    If Me.FrameColor.Value = 1 Then

        Me.Detail.BackColor = vbCyan

    ElseIf Me.FrameColor.Value = 2 Then

        Me.Detail.BackColor = vbMagenta

    ElseIf Me.FrameColor.Value = 3 Then

        Me.Detail.BackColor = vbYellow

    End If

End Sub

(4)    Checkbox সমূহে যথাক্রমে নিচের কোডগুলো লিখি।

Private Sub chkBlue_Click()

    If Me.chkBlue.Value = True Then

        Me.txtBlue.BackColor = vbBlue

    Else

        Me.txtBlue.BackColor = vbWhite

    End If

End Sub

Private Sub chkGreen_Click()

    If Me.chkGreen.Value = True Then

        Me.txtGreen.BackColor = vbGreen

    Else

        Me.txtGreen.BackColor = vbWhite

    End If

End Sub

Private Sub chkRed_Click()

    If Me.chkRed.Value = True Then

        Me.txtRed.BackColor = vbRed

    Else

        Me.txtRed.BackColor = vbWhite

    End If

End Sub

ফরমটি রান করিয়ে Option button ও Checkbox সমূহ সেলেক্ট করে program গুলো টেস্ট করি।

কোন জিজ্ঞাসা থাকলে কমেন্টে জানাবেন।

Read More

Excel Advance: How to filter data of a column that have specific word?

Suppose I have list of animal (See screenshot). I want to filter data which have word cat. So, for filtering data with word cat use the following formula.

=IFERROR(INDEX($A$5:$A$14,SMALL(IF(ISNUMBER(SEARCH("Cat",$A$5:$A$14)),ROW($A$5:$A$14)-ROW($A$4)),ROWS($A$5:$A5))),"")

*** Note: This is an array formula. So, you have to must press ‘Ctrl+Shift+Enter’ after typing formula.

Download sample file.

Read More

MS Access: Abbreviations of Acronym (কিভাবে list box এর data Filtering করবেন).

Post এর Heading অনেকের কাছে এলোমেলো লাগতে পারে। আমার আজকের টিউনের মূল উদ্দেশ্য হলো Microsoft Access এ কিভাবে List box এর ডাটা ফিল্টারিং করতে হয় এবং তার ব্যবহার।

     শুরুতে এখান থেকে Sample File টি Download করে নিন।         Zip File

(১) MS Access open করে একটি নতুন ফাইল নেই।

(২) নিচের চিত্রের মত করে একটি টেবিল ডিজাইন করি। টেবিলটি tblAbrAcr নামে save করি।

(৩) এখন ঐ টেবলের ডাটা নিয়ে একটি Query Design করি এবং qryAbrAcr নামে save করি। (নিচের চিত্রে কুয়েরী ডিজাইনের ধাপ সমূহ দেওয়া হলো।

(৪) একটি Form নেই এবং এতে একটি Text box এবং একটি List box নেই। Text box এর নাম txtSearch এবং List box এর নাম lstAbrAcr দিয়ে Form টি frmAbrAcr নামে সেভ করি।

(৫) List Box এর Row source হিসেবে qryAbrAcr Select করুন। (নিচের চিত্র দেখুন)

(৬) Text box এর “On Change” event এ নিচের কোড গুলো লিখুন। (নিচের চিত্রে কোড লিখার ধাপসমূহ দেওয়া হলো।

On Error GoToErrHandler

Me.lstAbrAcr.RowSource = "Select*from qryAbrAcr where acr like ('" &Me.txtSearch.Text& "*')"

Exit Sub

ErrHandler:

MsgBoxErr.Description, vbCritical, "Error"

Save করে code window close করে দিন। এবার form টি রান করে Text box এ আপনার কাঙ্খিত Acronym টি লিখুন, প্রতিটি Letter type করার সাথে সাথে List box এর ডাটা ফিল্টার হবে এবং টেক্সট বক্সের ডাটার সাথে মিলকৃত ডাটা সমূহ দেখাবে।

আপনি ইচ্ছা করলে Data add বা Edit ও করতে পারেন। কোন Abbreviation add বা Edit করতে চাইলে আমার Sample File এর Form এর Add/Edit Abbreviation এ ক্লিক করুন।

এখন Acronym box acronym এবং Abbreviation box এ abbreviation লিখে Add Button এ ক্লিক করুন, তাহলে Data টি ‍add হয়ে যাবে। আপনি ইচ্ছা করলে কোন ডাটা Edit বা Remove ও করতে পারেন।

যেকোন Query এর জন্য মেইল করুন harun24hr@gmail.com

Read More

MS Access: কিভাবে Navigation Pane এবং Ribbon Hide বা Show করবেন।

Sample File Download:    Hide_Show_Ribbon              Zip File (20 KB)

প্রথমে MS Access এ একটি নতুন ফাইল নিন। একটি Form ও Form এ চারটি Command button নিন। উপরের ছবির মত করে সাজিয়ে বাটনগুলোর নাম দিন।

Show Ribbon button এর কোড:

DoCmd.ShowToolbar "Ribbon", acToolbarYes

Hide Ribbon button এর কোড:

DoCmd.ShowToolbar "Ribbon", acToolbarNo

Show Navigation Pane button এর কোড:

Call DoCmd.SelectObject(acTable, , True)

Hide Navigation Pane button এর কোড:

Call DoCmd.NavigateTo("acNavigationCategoryObjectType")

Call DoCmd.RunCommand(acCmdWindowHide)

নিচের Screenshot এ কোডগুলো দেওয়া আছে।

Private Sub cmdHideNavPane_Click()

    Call DoCmd.NavigateTo("acNavigationCategoryObjectType")

    Call DoCmd.RunCommand(acCmdWindowHide)

End Sub

Private Sub cmdHideRibbon_Click()

    DoCmd.ShowToolbar "Ribbon", acToolbarNo

End Sub

Private Sub cmdShowNavPane_Click()

    Call DoCmd.SelectObject(acTable, , True)

End Sub

Private Sub cmdShowRibbon_Click()

    DoCmd.ShowToolbar "Ribbon", acToolbarYes

End Sub

Hide Ribbon ও Hide Navigation Pane এর কোড Startup Form এর “On Load” event লিখলে যখন ফাইলটি ওপেন করবেন তখন Ribbon ও Navigation Pane hide হয়ে যাবে।

বুঝতে কোথাও সমস্যা হলে মেইল করবেন। harun24hr@gmail.com

Read More

MS Access: ব্যবহার করে দোকানের Invoice তৈরির প্রোগ্রাম।

এখানে একটি MS Access প্রোগ্রাম ব্যবহার করে একটি Invoice System তৈরি করা হয়েছে। প্রোগ্রামটির কোড সমূহ উন্মুক্ত রাখা হয়েছে যাতে আগ্রহীরা কোডসমূহ দেখতে পারে এবং নিজেরা মডিফাই করতে পারে। আসুন দেখি কিভাবে এটি ব্যবহার করতে হবে।

প্রথমে এখান থেকে ফাইলটি ডাউনলোড করুন।   Zip File

(১) ফাইলটি খুললে নিচের ছবির মত Page দেখাবে।

(২) Invoice তৈরির জন্য Invoices Button এ ক্লিক করুন। নিচের ছবির মত একটি পেজ আসবে। নতুন Invoice তৈরির জন্য New button এ ক্লিক করুন। আপনার ডাটা এন্ট্রি করুন। এন্ট্রি শেষে Save button এ ক্লিক করুন। এখন View Button এ ক্লিক করে Invoice টি দেখতে ও প্রিন্ট করতে পারেন। Print Button এ Click করে সরাসরি প্রিন্ট করতে পারেন।এখানে Invoice টি দোকানের প্যাডে প্রিন্ট করার মত করে ডিজাইন করা হয়েছে (নিচের Screen Shot দেখুন)। আপনি চাইলে আপনার মত করে ডিজাইন করে নিতে পারেন। দোকান বা প্রতিষ্ঠানের নাম লগো ব্যবহার করতে পারেন।

(৩) কোন আইটেম Add বা Edit করার জন্য Add Item button এ ক্লিক করুন।

(৪) Model, Brand, Price ইত্যাদি Add বা Edit করার জন্য Add Models Button এ ক্লিক করুন। Price টা শুধুমাত্র দোকানদার নিজে দেখার জন্য।

(৫) সকল আইটেম একসাথে দেখার জন্য View All Items button এ ক্লিক করুন। এখান থেকেও আপনি বিভিন্ন তথ্য মডিফাই করতে পারবেন।

# Feel free to contact if you have any query about this post harun24hr@hotmail.com

Read More

MS Access: কিভাবে Dependable Combo Box তৈরি করবেন।

Dependable Combo Box: Dependable Combo Box হলো একটি Combo Box এর Value এর উপর ভিত্তি করে আরেকটি Combo Box এর Value List define করা। আমরা দেখব কিভাবে Microsoft Access এ Dependable Combo Box তৈরি করা যায়।

এখান থেকে Sample File Download করে নিন।

(1)  প্রধমে একটি নতুন অ্যাকসেস পজেক্ট নেই। নিচের ছবির মত একটি table design করি। টেবিলের নাম দেই tblItemBrandModel এবং এত নিচরে মত করে Data Entry করি (Sample File  এ ডাটা দেওয়া আছে)।

(2)  এবার একটি Form নেই এবং Form এ তিনটি Combo Box নেই। Combo Box গুলোর নাম দেই যথাক্রমে cboItem, cboBrand, cboModel. Form টি frmComboBoxes নামে সেভ করি।

(3)  cboItem এর property থেকে Data ----> Row Source এ ক্লিক করি।

(4)  নিচের চিত্রের মত Show Table ---> Select Table ----> Add ---> Close ---> Select “Item” in Field ‍select করি। Save & Close Query builder.

(5)  cboBrand এর জন্য cboItem এর মত করে Query builder window open করি এবং নিচের চিত্রের মত করে প্রথমে Brand এবং পরে Item Filed select করি। Item filed এর Criteria তে এই Expression লিখি [Forms]![frmComboBoxes]![cboItem] ‍  Save করে Close করে দিই।

(6)  এবার cboModel এর জন্য Query builder window open করে নিচের চিত্রের মত Field সমূহ সেলেক্ট করি। Item Field এর Criteria তে লিখি [Forms]![frmComboBoxes]![cboItem] এবং Model Field এর Criteria তে লিখি [Forms]![frmComboBoxes]![cboBrand]     Save করে Close করি।

(7)  এবার cboItem এর After Update Event এ নিচের কোড লিখি  (নিচের চিত্র Follow করুন)

On Error GoTo ErrHandler

Me.cboBrand.Requery

Me.cboModel.Requery

Exit Sub

ErrHandler:

MsgBox Err.Description, vbCritical, "Error"

(8)  এবার cboBrand এর After Update Event এ নিচের কোড লিখি  (নিচের চিত্র Follow করুন)

On Error GoTo ErrHandler

Me.cboModel.Requery

Exit Sub

ErrHandler:

MsgBox Err.Description, vbCritical, "Error"

 

Save করে window টি close করে দিই।

(9)   এখন form টি Run করে cboItem থেকে যেকোন Item Select করি এবং cboBrand এ ক্লিক করি। আমরা দেখব যে, cboBrand এ শুধুমাত্র ঐ Item এর Brand সমূহ দেখাচ্ছে। cboBrand এ যেকোন Brand Select করে cboModel এ ক্লিক করি। আমরা দেখব যে cboModel এ শুধুমাত্র ঐ Item এর Selected Brand এর Model সমূহ দেখাচ্ছে।

যেকোন Query এর জন্য মেইল করুন harun24hr@gmail.com

Read More

MS Access: কিভাবে শুধুমাত্র ID select করে ঐ আডির সাথে সম্পর্কিত ডাটা অটোমেটিক পরবর্তী ফিল্ড সমূহে বসাবেন।

এখানে ব্যবহৃত সকল ডাটা কাল্পনিক।

আমরা যারা MS Access Database নিয়ে কাজ করি তাদের হয়তো আজকের টিউনটি কাজে আসতে পারে। আজকের টিউনের উদ্দেশ্য হলো কিভাবে শুধুমাত্র ID select করে ঐ আডির সাথে সম্পর্কিত ডাটা অটোমেটিক পরবর্তী ফিল্ড সমূহে বসাবেন তা জানা। আমরা প্রথমে একটি টেবিলে Employee Information entry করব। তারপর আরেকটা টেবিলে Employee রা যে সমস্ত ডিভাইস স্টোর থেকে নিয়ে থাকে তার হিসাব রাখার জন্য ডাটা এন্ট্রি করব। এই ডাটা এন্ট্রি করার সময় আমরা শুধু Employee এর আডি সেলেক্ট করব, সেলেক্ট করার পর তার অন্যান্য তথ্য Automatic চলে আসবে।

Download: Sample File, Video (HighResolution 20MB), Video(Low Resolution 8MB)

For any Query email me harun24hr@gmail.com

Read More

MS Access Advance: User Log-in form. কিভাবে মাইক্রোসফট অফিস অ্যাকসেস এ লগ-ইন ফরম বানাবেন।

মাইক্রোসফট অফিস অ্যাকসেস এ অনেকভাবে লগ-ইন ফরম বানানো যায়। আবার Access এ ফাইল ওপেন করার জন্য password ও সেট করা যায় ডিফল্ট ভাবে। আমি আজকে দেখাব DLookup function ব্যবহার করে কিভাবে Log-in Form বানানো যায়।বুঝার সুবিধার জন্য নিচের লিংক থেকে sample file ডাউনলোড করুন।

Sample File: Login Form Sample    Zip File (273 KB)

(1)    প্রথমে Access open করে একটি নতুন ডাটাবেজ তৈরি করুন। নিচের চিত্রের মত করে একটি টেবিল তৈরি করুন। Password field এর Input Mask এ অবশ্যই password সেলেক্ট করবেন। Input Mask এ password সেলেক্ট করলে আপনি যখন password টাইপ করবেন তখন স্টার (*) mark show করবে। অন্যথায় যা টাইপ করবেন তাই দেখাবে।

(2)    টেবিলে User এবং Password add করুন। আমি sample file এ user= admin, password= admin ব্যবহার করেছি।

(3)    এখন একটি form design এ দুটি Text Box এবং একটি Command Button নিন। Text Box দুটির নাম দিন যথাক্রমে txtUserID এবং txtPassword. Command Button এর নাম দিন cmdLogin. txtPassword এর property sheet থেকে Data tab এ গিয়ে Input Mask এ password select করুন। নিচের চিত্র দেখুন।

(4)    আরেকটি form design করুন এর নাম দিন frmMain.

(5)    cmdLogin button এর click event এ নিচের কোডসমূহ লিখুন।

On Error GoTo ErrHandler

Dim UserLookUp As Variant

Dim PasswordLookup As Variant

   

    UserLookUp = DLookup("[UserID]", "[tblUsers]", "[UserID]='" & Me.txtUserID & "'")

    PasswordLookup = DLookup("[Password]", "[tblUsers]", "[UserID]='" & Me.txtUserID & "'")

                

        If (UserLookUp = Me.txtUserID) And (PasswordLookup = Me.txtPassword) Then

              DoCmd.Close acForm, "frmLogin", acSaveYes

              DoCmd.OpenForm "frmMain"

        Else

            MsgBox "Invalid Login!!!" & vbCrLf & "Check your user name and password and then try again.", vbCritical, "Invalid Login"

        End If

Exit Sub

ErrHandler:

MsgBox Err.Description, vbCritical, "Error"

(6)    এবার Form টি run করান এবং আপনার user ও password দিয়ে test করুন।

কোডের ব্যাখা:

প্রথমে এরর হান্ডলিং মেকানিজম ব্যবহার করা হয়েছে। Dlookup function এর মাধ্যমে user এবং password খোজা হয়েছে। User এবং password match করলে ‍second form open হবে অথবা Error message দেখাবে।

Any problem, Please comment in comment. You can also mail me at harun24hr@gmail.com

Read More

MS Access: কিভাবে দুইটি তারিখের মধ্যে ডাটা ফিল্টারিং করবেন। How to filter data between two dates.

আমরা আজকে দেখব কিভাবে দুই তারিখের মধ্যে ডাটা ফিল্টারিং করা যায়। আমি এখানে sample হিসাবে কিছু বিক্রয়ের তথ্য ব্যবহার করেছি। আপনারা আপনাদের ডাটা ব্যবহার করে কাজটি করতে পারেন। আমি ভিডিওতে সম্পূর্ন কাজটা দেখিয়েছি। ভিডিওটা ডাউনলোড করে দেখবেন, তাহলে আপনারা পরিস্কার ভাবে বুঝতে পারবেন।

Sample File Download: Data Filtering Between Two Date (968 KB)                Zip File (52 KB)

Video Tutorial Download:  Data Filtering Between Two Date

*** নিচের কোন step না বুঝতে পারলে অবশ্যই video টা দেখবেন।

(১) প্রথমে নিচের চিত্রের মত করে একটি টেবিল ডিজাইন করুন। টেবিলের নাম দিন tblSalesData

(২) এরপর টেবিলে কিছু ডাটা এন্ট্রি করুন।

(৩) এখন একটা ফরম ডিজাইন করুন, যেখানে দুইটা Textbox এবং একটা Command Button নিন। Textbox দুটোর নাম দিন যথাক্রমে txtStartDate এবং txtEndDate. Form টা frmFilter নামে সেভ করুন।

(4) এখন একটা Query design করুন (Quer এর নাম দিন qrySalesData) নিচের চিত্রের মত করে এবং ‍SalesDate এর criteria তে এই লাইন add করুন। Between [Forms]![frmFilter]![txtStartDate] And [Forms]![frmFilter]![txtEndDate]

এই লাইনটাই ডাটা ফরম এর মধ্যে দেওয়া তারিখ অনুযায়ী ফিল্টার করবে। এখানে frmFilter এর যায়গায় আপনাদের ফরমের নাম ব্যবহার করবেন (যদি ফরম অন্য নামে সেভ করে থাকেন)।

(৫) এখন নিচের ছবির মত করে একটি report design করুন। Report এর Record Source হিসাবে Query (qrySalesData) ব্যবহার করুন।

(৬) frmFilter এ যে command button নিয়েছেন তার “On Click” event এ নিচের ছবির মত করে Macro design করুন। এরপর Form টা চালু করে টেক্সট বক্সে তারিখ বসিয়ে কমান্ড বাটনে ক্লিক করুন। আপনার দেয়া তারিখ অনুযায়ী ডাটা report এ দেখাবে।

*** কোন জিজ্ঞাসা থাকলে কমেন্টে অথবা ইমেইলে জানাবেন। আমার ই-মেইল: harun24hr@gmail.com

Read More

Excel Advance: কিভাবে একটি sheet এর data অন্যের দেখা থেকে Protect করবেন password সেট করার মাধ্যমে।

আজকে আমার পোষ্টের বিষয় হলো কিভাবে একটি শীটের data password এর মাধ্যমে protect করা যায়, যাতে অন্য কেউ দেখতে না পারে। অনেকে মনে করতে পারে আমিতো শীটে password দিলেই কেউ দেখতে পারবেনা। কিন্তু না আমার আজকের টিউনের বিষয়টি একটু অন্যরকম। Suppose একটি workbook এ একাধিক শীট আছে, যেখানে একেকটি শীটে একক জন data entry করে। কিন্তু একটি শীটে কিছু important information আছে যা সবার দেখার জন্য না। আমি শুধু ঐ শীটটিতে password দিয়ে রাখব যাতে অন্যরা অন্যান্য সব শীটের ডাটা দেখতে ও এডিট করতে পারলেও ঐ শীটের ডাটা দেখতে ও এডিট করতে না পারে। আসুন দেখি কাজটি কিভাবে করা যায়।

Downloadsample file from here    (Zip File)

(১) একটি Excel file নিই এবং এতে তিনটি শীট নিই এবং যথাক্রমে নাম দেই Main, HiddenSheet, AnotherSheer.

(২) HiddenSheet এ আপনি কিছু ডাটা entry করুন। Keyboard এর Alt+F11 চেপে Excel code window open করুন। নিচের চিত্রের মত বাম পাশের project explorer window এর ThisWorkbook এ double click করুন এবং চিত্রের ন্যায় ২ ও ৩ নং ধাপে Workbook এবং SheetActivate select করুন। এখন চিত্রের মত করে নিচের কোড টুকো লিখুন (Copy Paste করতে পারেন)।

Dim MySheetName As String

MySheetName = "HiddenSheet" 'The sheed which I want to hide.

If Application.ActiveSheet.Name = MySheetName Then

    Application.EnableEvents = False

     Application.ActiveSheet.Visible = False

      response = Application.InputBox("Password", "Enter Password", "", Type:=2)

    If response = "123456" Then 'Unhide Password.

        Application.Sheets(MySheetName).Visible = True

        Application.Sheets(MySheetName).Select

    End If

End If

Application.Sheets(MySheetName).Visible = True

Application.EnableEvents = True

এবং workbook_open() এ {৩ নং স্টেপে SheetActivate এর যায়গায় open select করুন} নিচের লাইনটা শুধু ‍লিখুন

Sheets("Main").Select

(৩) সবশেষে ফাইলটি Excel Macro-Enabled Workbook হিসেবে save করুন। (Office Button ----> Save As ----> Excel Macro-Enabled Workbook

File টি Close করে পুনরায় চালু করুন। HiddenSheet এই শীটে ক্লিক করলেই আপনার কাছে password চাবে। password সঠিক হলে আপনি ডাটা দেখতে এবং এডিট করতে পারবেন, অন্যথায় পারবেন না।

 

For any types of query about this post mail me at harun24hr@gmail.com

Read More

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

Read More