Need help to data for Invoice data entry

%3CLINGO-SUB%20id%3D%22lingo-sub-2806338%22%20slang%3D%22en-US%22%3ENeed%20help%20to%20data%20for%20Invoice%20data%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20tried%20to%20use%20If%20formula%20in%20vba%20to%20display%20data%20on%20the%20left(please%20refer%20to%20attached%20image)%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2214e82207-91b5-4584-9ee9-6413262acf29.jpg%22%20style%3D%22width%3A%20515px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314636i3767E8876E62DC95%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2214e82207-91b5-4584-9ee9-6413262acf29.jpg%22%20alt%3D%2214e82207-91b5-4584-9ee9-6413262acf29.jpg%22%20%2F%3E%3C%2FSPAN%3E%20to%20data%20on%20the%20right(if%20same%20date%2C%20up%20to%204%20data%20will%20be%20in%20the%20same%20row).%20but%20my%20formula%20is%20not%20working.%20can%20anyone%20help%20me%20with%20it%3F%20Thank%20you%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFunction%20Invoiced(I%20As%20String)%20As%20String%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20ActiveCell.Offset(0%2C%20-2)%20%3D%20ActiveCell.Offset(-1%2C%20-2)%20Then%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20ActiveCell.Offset(-1%2C%20-2)%20%3D%20ActiveCell.Offset(-2%2C%20-2)%20Then%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20ActiveCell.Offset(-2%2C%20-2)%20%3D%20ActiveCell.Offset(-3%2C%20-2)%20Then%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20ActiveCell.Offset(-3%2C%20-2)%20%3D%20ActiveCell.Offset(-4%2C%20-2)%20Then%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EInvoiced%20%3D%20ActiveCell.Offset(0%2C%20-2)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(0%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-1%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-2%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-3%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-4%2C%20-1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EElse%20Invoiced%20%3D%20ActiveCell.Offset(0%2C%20-2)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(0%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-1%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-2%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-3%2C%20-1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20If%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EElse%20Invoiced%20%3D%20ActiveCell.Offset(0%2C%20-2)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(0%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-1%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-2%2C%20-1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20If%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EElse%20Invoiced%20%3D%20ActiveCell.Offset(0%2C%20-2)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(0%2C%20-1)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(-1%2C%20-1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20If%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EElse%20Invoiced%20%3D%20ActiveCell.Offset(0%2C%20-2)%20%2B%20%22%20%22%20%2B%20ActiveCell.Offset(0%2C%20-1)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20If%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20Function%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2806338%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806370%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20data%20for%20Invoice%20data%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173297%22%20target%3D%22_blank%22%3E%40tingxia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20ways%20to%20achieve%20that.%3C%2FP%3E%3CP%3EUsing%20Array%20formulas.%26nbsp%3B%20On%20H1%20type%20the%20following%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DUNIQUE(A1%3AA16)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOn%20I1%20cell%20the%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCONCAT(%22%20%22%20%26amp%3BUNIQUE(FILTER(%24B%241%3A%24B%2416%2C%24A%241%3A%24A%2416%3DH1)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20VBA%20and%20Dictionary%20Library%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20InvoiceDataGrouping()%0A%20%20%20%20Dim%20DataSet%20As%20Variant%2C%20Counter%20As%20Long%2C%20Dict%20As%20Object%0A%20%20%0A%20%20%20%20'Set%20Dict%20%3D%20New%20Scripting.Dictionary%20'Early%20Binding%0A%20%20%20%20Set%20Dict%20%3D%20CreateObject(%22Scripting.Dictionary%22)%20'Late%20Binding%0A%20%20%20%20%0A%20%20%20%20'stores%20in%20an%20array%20all%20the%20data%20from%20columns%20A%20and%20B%2C%0A%20%20%20%20'starting%20at%20A1%20and%20up%20to%20the%20last%20row%20with%20data%20from%20column%20C.%0A%20%20%20%20DataSet%20%3D%20Sheets(%22WithVBA%22).Range(%22A1%22%2C%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(3)).Value2%0A%20%20%0A%20%20%20%20For%20Counter%20%3D%201%20To%20UBound(DataSet)%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20Dict(DataSet(Counter%2C%201))%20%3D%20Dict(DataSet(Counter%2C%201))%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2B%20%22%20%22%20%26amp%3B%20DataSet(Counter%2C%202)%0A%20%20%20%20Next%0A%20%20%0A%20%20%20%20Sheets(%22WithVBA%22).Range(%22H1%22).Resize(Dict.Count%2C%202).Value%20%3D%20Application.Transpose(Array(Dict.keys%2C%20Dict.items))%0A%20%20%20%20%0A%20%20%20%20Set%20Dict%20%3D%20Nothing%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BFind%20attachment%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806401%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20data%20for%20Invoice%20data%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%2C%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20to%20display%20date%20as%20dd.mm.yy%20and%20for%20data%20of%20the%20same%20date%20to%20be%20displayed%20maximum%20of%204%20a%20row%20and%20the%20others%20will%20fall%20to%20the%20next%20row%20as%20shown%20in%20the%20picture%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-10-03%20at%2012.05.40%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314638iCDD045CC6720804D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-10-03%20at%2012.05.40%20PM.png%22%20alt%3D%22Screen%20Shot%202021-10-03%20at%2012.05.40%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I tried to use If formula in vba to display data on the left(please refer to attached image)14e82207-91b5-4584-9ee9-6413262acf29.jpg to data on the right(if same date, up to 4 data will be in the same row). but my formula is not working. can anyone help me with it? Thank you

 

Function Invoiced(I As String) As String

If ActiveCell.Offset(0, -2) = ActiveCell.Offset(-1, -2) Then

If ActiveCell.Offset(-1, -2) = ActiveCell.Offset(-2, -2) Then

If ActiveCell.Offset(-2, -2) = ActiveCell.Offset(-3, -2) Then

If ActiveCell.Offset(-3, -2) = ActiveCell.Offset(-4, -2) Then

Invoiced = ActiveCell.Offset(0, -2) + " " + ActiveCell.Offset(0, -1) + " " + ActiveCell.Offset(-1, -1) + " " + ActiveCell.Offset(-2, -1) + " " + ActiveCell.Offset(-3, -1) + " " + ActiveCell.Offset(-4, -1)

Else Invoiced = ActiveCell.Offset(0, -2) + " " + ActiveCell.Offset(0, -1) + " " + ActiveCell.Offset(-1, -1) + " " + ActiveCell.Offset(-2, -1) + " " + ActiveCell.Offset(-3, -1)

End If

Else Invoiced = ActiveCell.Offset(0, -2) + " " + ActiveCell.Offset(0, -1) + " " + ActiveCell.Offset(-1, -1) + " " + ActiveCell.Offset(-2, -1)

End If

Else Invoiced = ActiveCell.Offset(0, -2) + " " + ActiveCell.Offset(0, -1) + " " + ActiveCell.Offset(-1, -1)

End If

Else Invoiced = ActiveCell.Offset(0, -2) + " " + ActiveCell.Offset(0, -1)

End If

End Function

1 Reply

@tingxia 

There are many ways to achieve that.

Using Array formulas.  On H1 type the following formula:

=UNIQUE(A1:A16)

On I1 cell the formula

=CONCAT(" " &UNIQUE(FILTER($B$1:$B$16,$A$1:$A$16=H1)))

 

 

Using VBA and Dictionary Library 

Option Explicit

Sub InvoiceDataGrouping()
    Dim DataSet As Variant, Counter As Long, Dict As Object
  
    'Set Dict = New Scripting.Dictionary 'Early Binding
    Set Dict = CreateObject("Scripting.Dictionary") 'Late Binding
    
    'stores in an array all the data from columns A and B,
    'starting at A1 and up to the last row with data from column C.
    DataSet = Sheets("WithVBA").Range("A1", Range("B" & Rows.Count).End(3)).Value2
  
    For Counter = 1 To UBound(DataSet)
        
        Dict(DataSet(Counter, 1)) = Dict(DataSet(Counter, 1)) _
                                    + " " & DataSet(Counter, 2)
    Next
  
    Sheets("WithVBA").Range("H1").Resize(Dict.Count, 2).Value = Application.Transpose(Array(Dict.keys, Dict.items))
    
    Set Dict = Nothing
End Sub

 Find attachment