INDEX-MATCH with Duplicate Values

pcardenasm · Dec 9, 2023

Hi

I have a code that works well for copy data to some sheets to main sheet, I realized in some sheets are duplicated values for the same date, I used an expression to count them in each sheet (column A)
I tried to use INDEX and MATCH (two criteria) with new created ranges, but it doesn't work. Or maybe i should use another function?

TEP-EjBD-F.RevB.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

137

1

24/09/2020

Intercontrol Levante S.A.

1,850

Puntual

MEN-LMA-016

138

2

24/09/2020

Intercontrol Levante S.A.

2,050

Compuesta

MEN-LMA-016

139

3

24/09/2020

Intercontrol Levante S.A.

1,850

Puntual

MEN-LMA-016

140

4

24/09/2020

Intercontrol Levante S.A.

2,070

2,050

1,270

1,920

1,140

Compuesta + Puntual

MEN-LMA-016 + MEN-LMA-057

141

1

01/10/2020

Intercontrol Levante S.A.

2,120

2,130

Compuesta

MEN-LMA-016

142

1

08/10/2020

Intercontrol Levante S.A.

2,090

Compuesta

MEN-LMA-016

143

1

15/10/2020

Intercontrol Levante S.A.

2,090

2,130

1,840

1,850

2,300

1,630

Compuesta + Puntual

MEN-LMA-016 + MEN-LMA-057

144

1

22/10/2020

Intercontrol Levante S.A.

2,070

2,060

Compuesta

MEN-LMA-016

145

2

22/10/2020

Intercontrol Levante S.A.

1,870

Puntual

MEN-LMA-016

146

1

27/10/2020

Intercontrol Levante S.A.

2,100

2,080

Compuesta

MEN-LMA-016

147

1

04/11/2020

Intercontrol Levante S.A.

2,010

Compuesta

MEN-LMA-016

148

1

13/11/2020

Intercontrol Levante S.A.

812

Compuesta

MEN-LMA-016

149

1

20/11/2020

Intercontrol Levante S.A.

894

Compuesta

MEN-LMA-016

150

1

24/11/2020

Intercontrol Levante S.A.

889

Compuesta

MEN-LMA-016

151

1

04/12/2020

Intercontrol Levante S.A.

971

1,230

Compuesta

MEN-LMA-016

152

1

11/12/2020

Intercontrol Levante S.A.

2,050

1,990

Compuesta

MEN-LMA-016

153

1

17/12/2020

Intercontrol Levante S.A.

2,090

1,850

1,700

2,500

1,250

Compuesta + Puntual

MEN-LMA-016 + MEN-LMA-057

154

2

17/12/2020

Intercontrol Levante S.A.

1,880

Puntual

MEN-LMA-016

155

1

23/12/2020

Intercontrol Levante S.A.

1,840

Puntual

MEN-LMA-016

156

2

23/12/2020

Intercontrol Levante S.A.

1,930

2,020

Compuesta

MEN-LMA-016

Conductividad

Cell Formulas
Range	Formula
A137:A156	A137	=COUNTIF($B$12:B137,B137)

VBA Code:

Option Explicit
Sub CopyValues()
    Dim cont As Long
    Dim ultlinea As Long
    Dim fecha As Date
    Dim muestras As Variant
    Dim analisis As Variant
    Dim parametro As Variant
    Dim tipo As Variant
    Dim metodo As Variant
    Dim influente As Variant
    Dim MEnt As Variant
    Dim MEntf As Variant
    Dim MA As Variant
    Dim MAf As Variant
    Dim MB As Variant
    Dim MBf As Variant
    Dim MC As Variant
    Dim MCf As Variant
    Dim ML As Variant
    Dim MLf As Variant
    Dim m As Long
    Dim y As Long
    Dim EfluentTerc As Variant                    'Cuando puede ser texto o número
    Dim EfluentTercF As Variant
    Dim rango As Variant
    Dim fila As Long                              '<- added: wasn't declared
    Dim myArray As Variant                        '<- added: list of sheets with data to be copied (ordered)
    Dim shtName As Variant                        '<- added
    Dim firstRow As Long                          '<- added2

    Dim MAf2 As Variant
    
    Dim contador As Long

    Dim rngfecha As Range           'added dic23
    Dim rngcont As Range            'added dic23
    Dim rngmuest As Range           'added dic23

    Application.ScreenUpdating = False            '<- added: avoids screen flickering
    firstRow = 3                                  '<- added2
    ultlinea = sheets("BD").Range("A" & Rows.Count).End(xlUp).Row '<- added2
    If ultlinea < 3 Then ultlinea = 3             '<- added2
    sheets("BD").Range("A3:O" & ultlinea).ClearContents '<- added2
    myArray = Array("pH", "Temperatura", "Conductividad", "Oxígeno", "Turbidez", "Sólidos en suspensión", "Sólidos Susp. Volatiles", "DQO", "DBO5", "Nitrógeno", "Fósforo", "Ortofosfatos")
    For Each shtName In myArray                   '<- added: loop on all sheets listed in myArray
        '<- changed: from here to end changed all "Fósforo" to shtName
        sheets(shtName).Select
        'Range("B12").Select
        Range(Range("A12:B12"), Range("A12:B12").End(xlDown)).Copy '<- changed2
        'Sheets("BD").Select
        'Range("A" & firstRow).Select
        'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        sheets("BD").Range("A" & firstRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<- changed2
        'Range("A3").Select
        'Sheets(shtName).Select
        'Application.CutCopyMode = False
        'Range("B12").Select
        ultlinea = sheets("BD").Range("A" & Rows.Count).End(xlUp).Row
        fila = sheets(shtName).Range("B12").End(xlDown).Row 'última fila de la hoja a copiar
        
        Set rango = sheets(shtName).Range("B12:N" & fila) 'define el rango de la hoja a copiar
        
        Set rngcont = sheets(shtName).Range("A12:A" & fila)     'added dic23
        Set rngfecha = sheets(shtName).Range("B12:B" & fila)    'added dic23
        Set rngmuest = sheets(shtName).Range("C12:C" & fila)    'added dic23
        
        For cont = firstRow To ultlinea           '<- changed2
            contador = sheets("BD").Cells(cont, 1)              'added dic23
            fecha = sheets("BD").Cells(cont, 2)
            m = Month(fecha)
            y = Year(fecha)
            muestras = Application.Index(rngmuest, Application.Match(contador & CLng(CDate(fecha)), rngcont & rngfecha, 0)) 'it doesn't work
            
            'i want to do the same for these expressions
            analisis = Application.VLookup(CLng(CDate(fecha)), rango, 3, False)
            parametro = sheets(shtName).Name
            tipo = Application.VLookup(CLng(CDate(fecha)), rango, 12, False)
            metodo = Application.VLookup(CLng(CDate(fecha)), rango, 13, False)
            EfluentTerc = Application.VLookup(CLng(CDate(fecha)), rango, 5, False)
            If Application.WorksheetFunction.IsText(EfluentTerc) Then
                EfluentTercF = Mid(EfluentTerc, 2, 4) / 2
            Else
                EfluentTercF = EfluentTerc
            End If
            MEnt = Application.VLookup(CLng(CDate(fecha)), rango, 6, False)
            If Application.WorksheetFunction.IsText(MEnt) Then
                MEntf = Mid(MEnt, 2, 4) / 2
            Else
                MEntf = MEnt
            End If
            MA = Application.VLookup(CLng(CDate(fecha)), rango, 7, False)
            If Application.WorksheetFunction.IsText(MA) Then
                MAf = Mid(MA, 2, 4) / 2
            Else
                MAf = MA
            End If
            MB = Application.VLookup(CLng(CDate(fecha)), rango, 8, False)
            If Application.WorksheetFunction.IsText(MB) Then
                MBf = Mid(MB, 2, 4) / 2
            Else
                MBf = MB
            End If
            MC = Application.VLookup(CLng(CDate(fecha)), rango, 9, False)
            If Application.WorksheetFunction.IsText(MC) Then
                MCf = Mid(MC, 2, 4) / 2
            Else
                MCf = MC
            End If
            ML = Application.VLookup(CLng(CDate(fecha)), rango, 10, False)
            If Application.WorksheetFunction.IsText(ML) Then
                MLf = Mid(ML, 2, 4) / 2
            Else
                MLf = ML
            End If
            
            
            
            If IsNumeric(MEntf) Then
                influente = MEntf
                If IsEmpty(MEntf) And IsNumeric(EfluentTercF) Then
                    influente = EfluentTercF
                    If IsEmpty(MEntf) And IsEmpty(EfluentTercF) Then influente = MLf
                End If
            End If
            
            
            If IsNumeric(MAf) Then
            
                MAf2 = Format(Round(MAf, 2), "#,##0.00")
            
                If IsEmpty(MAf) Then
                    MAf2 = ""
                End If
            End If
            

            
            sheets("BD").Cells(cont, 3) = m
            sheets("BD").Cells(cont, 4) = y
            sheets("BD").Cells(cont, 5) = muestras
            sheets("BD").Cells(cont, 6) = analisis
            sheets("BD").Cells(cont, 7) = parametro
            sheets("BD").Cells(cont, 8) = tipo
            sheets("BD").Cells(cont, 9) = metodo
            sheets("BD").Cells(cont, 10) = EfluentTercF
            sheets("BD").Cells(cont, 11) = influente
            sheets("BD").Cells(cont, 12) = MAf2
            sheets("BD").Cells(cont, 13) = MBf
            sheets("BD").Cells(cont, 14) = MCf
            sheets("BD").Cells(cont, 15) = MLf
        
        Next cont
        firstRow = ultlinea + 1                   '<- added2
        Application.CutCopyMode = False           '<- moved2
    Next shtName                                  '<- added
    Application.ScreenUpdating = True             '<- added
    sheets("BD").Select
    Range("A3").Select
    MsgBox "Valores copiados exitosamente", vbInformation, "Copiar" '<- changed
End Sub

file

Thank you

SanjayGMusafir · Dec 11, 2023

pcardenasm said:
I tried to use INDEX and MATCH (two criteria) with new created ranges, but it doesn't work

What do you want to do with Index-Match you haven't explained. Having multiple entries for one date is no problem until you have some second unique way of identifying the data you are looking for.

pcardenasm said:
Or maybe i should use another function?

It all depends on purpose and kind of lookup you want to perform there can be multiple solution to your problem with Excel 365 in your hand.

Explain better what you want to achieve...

pcardenasm · Dec 17, 2023

Hi!

Thank you for your response.

The code is for copy information from some sheets (12) to BD. In sheet BD should be all the information from the other sheets (manually registered) with its format and must be updated (BD) every time data is entered in the other sheets. Headers in BD are fixed.

The code works well, but then I realized in some sheets for the same date (duplicated dates) are different values registered, and the code just identify the first value (not all values registered as I would like them to be copied) when i use the function vlookup.

I used an expression to count them in each sheet (column A), I tried to use INDEX and MATCH (with two criteria) with new created ranges (rngcont, rngfecha, rngmuest), but it doesn't work (muestras). I want to do the same for 'analisis, parametro, tipo, metodo, EfluentTerc, MEnt, MA, MB, MC, ML'

Patricia CM

SanjayGMusafir · Dec 17, 2023

What I understand from the information you have provided is probably you need a better method of dala consolidation. Check the video below it can help you in some useful way -

SanjayGMusafir · Dec 17, 2023

OR this one explains combining data from multiple sheets to one sheet

INDEX-MATCH with Duplicate Values

pcardenasm

New Member

Excel Facts

SanjayGMusafir

Well-known Member

pcardenasm

New Member

SanjayGMusafir

Well-known Member

SanjayGMusafir

Well-known Member

Similar threads

Forum statistics

Share this page

INDEX-MATCH with Duplicate Values

pcardenasm

New Member

Excel Facts

SanjayGMusafir

Well-known Member

pcardenasm

New Member

SanjayGMusafir

Well-known Member

SanjayGMusafir

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock