Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Remove Duplicates & Create Word Documents For Each Group

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Remove Duplicates & Create Word Documents For Each Group

    Hi,

    Attached is a spreadsheet with raw data. The columns contain the units of a textbook, and the rows contain educational state standards that correspond with those units. I need to create a Word document for IL and GA, converting the rows of data into a listing of standards for each unit. I also need to remove all duplicates.

    For this I need a macro written. If necessary, please include a brief description of your process in your return email or in an attached document.

    The completed Word document for NJ is included as an example. I am willing to pay $10 for this task.

    Thanks
    Attached Files

  • #2
    Re: Remove Duplicates & Create Word Documents For Each Group

    Hi,

    Public TBs      As Variant
    Public TBCOUNTs As Variant
    Sub GenerateDocs()
    ' add a reference to the Word-library
    ' go to tools > reference > check Microsoft Word X.0 Object Library. where X is the version #
    Dim a, i As Long, MyTBs, ws As Worksheet, j As Long
    Dim x, c As Long, k As Long, Flg As Boolean
    Dim dic As Object
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document

    Set ws = ActiveSheet
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordDoc = wordApp.Documents.Add
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = vbTextCompare
    a = ws.UsedRange
    MyTBs = Array("IL", "GA") 'add your preferences here
    TBCOUNT ws.Range("a3", ws.Range("a" & Rows.Count).End(xlUp)).Value
    ReDim w(1 To Rows.Count, 1 To 1)
    For i = 3 To UBound(a, 1)
    x = Application.Match(a(i, 1), MyTBs, 0)
    If Not IsError(x) Then Flg = True
    If Flg Then
    With Application
    k = .Index(TBCOUNTs, .Match(a(i, 1), TBs, 0))
    End With
    For c = 2 To UBound(a, 2)
    wordDoc.Content.InsertParagraphAfter
    wordDoc.Content.InsertAfter a(2, c)
    For j = i To k + i - 1
    If Not IsEmpty(a(j, c)) Then
    If Not dic.Exists(a(j, c)) Then
    wordDoc.Content.InsertAfter a(j, c)
    wordDoc.Content.InsertParagraphAfter
    End If
    End If
    Next
    dic.removeall
    Next
    Flg = False
    i = i + k
    End If
    Next
    End Sub
    Private Sub TBCOUNT(a)
    Dim i As Long
    With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For i = 1 To UBound(a, 1)
    If Not IsEmpty(a(i, 1)) Then
    If Not .Exists(a(i, 1)) Then
    .Add a(i, 1), 1
    Else
    .Item(a(i, 1)) = .Item(a(i, 1)) + 1
    End If
    End If
    Next
    TBs = .keys
    TBCOUNTs = .items
    End With
    End Sub
    Kris

    ExcelFox

    Comment

    Trending

    Collapse

    • maichal
      auto generate id in user form save sale purchase data on sale & purchase sheet
      maichal
      i am add add sale transaction & add purchase transaction command button & the id was Auto generate in text box 1 if i am choose sale in combo box 1 than sale-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add sale transaction command button the data was add on the sale sheet,if i am choose Purchase in combo box 1 than Purc-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add Purchase transaction command button the data was add on the Purchase sheet, please solve this sir, after add the information in sale & purchase excel sheet, i am choose sale in combo box 1 the id was auto generate in id text box 1, than i am choose the name in combo box 2, if i am choose sale in combo box 1 than combo box 2 show only sale Customer...
      2 days ago
    • DiogoCuba
      US$20 to Automate Crew Roster
      DiogoCuba
      Hi, I have a Crew Roster that controls the crew changes of a certain vessel and I would like to automate it to avoid wasting time with manual copy and paste.

      Anybody willing to help me?

      Some of the actions I need:
      • Create a Pax list and a Flight Manifest based on the personnel assigned to embark and disembark;
      • Generate a Daily POB based on the names that are onboard (Including the dates of embark and a counter to count how many days the person is onboard);
      • Compare the Daily POB to a criteria to see if the safe manning is compliant or not;
      • Create a PAX LIST for the next 7 days automatically so that I can see who is crew change and who is not - this should be triggered based on the dates;
      • Auto generate Flight Manifest when I click a button and input the
      ...
      June 27th, 2017, 09:47
    Working...
    X