Getting position of an item in a list

[JumboCactuar]

Hi,
sorry title doesn't really explain it well:

I have data like below (100k + rows)

Item Code	Seller	Price
FR11	ABCTech	16.49
FR11	KLG	15.19
FR11	JustPro	15.11
FR11	MapLtd	14.99
GR44	KLG	38.99
GR44	ABCTech	36.49
GR44	JustPro	35.99
ZD16	KLG	12.99
ZD16	JustPro	12.89
ZD16	ABCTech	12.49
PG20	JustPro	22.79
PG20	ABCTech	21.99
PG20	MapLtd	19.99
PG20	KLG	18.49

It is always sorted like above (grouped by item, then by price high to low)

I need to find out the following:
Which Position JustPro is in the list and price difference from 1st position

From the above - expected output is:

Item Code	Position	Price Difference
FR11	3	1.38
GR44	3	3
ZD16	2	0.1
PG20	1	0

also if possible to calculate the price difference between 1st + 2nd for every item

doable with just formulas ?

appreciate any help

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	Item Code	Seller	Price		Item Code	Position	Price Difference
2	FR11	ABCTech	16.49		FR11	3	1.38
3	FR11	KLG	15.19		GR44	3	3
4	FR11	JustPro	15.11		ZD16	2	0.1
5	FR11	MapLtd	14.99		PG20	1	0
6	GR44	KLG	38.99
7	GR44	ABCTech	36.49
8	GR44	JustPro	35.99
9	ZD16	KLG	12.99
10	ZD16	JustPro	12.89
11	ZD16	ABCTech	12.49
12	PG20	JustPro	22.79
13	PG20	ABCTech	21.99
14	PG20	MapLtd	19.99
15	PG20	KLG	18.49
Sheet5

Cell Formulas
Range	Formula
F2:F5	F2	=MATCH(E2&"JustPro",$A$2:$A$15&$B$2:$B$15,0)-MATCH(E2,$A$2:$A$15,0)+1
G2:G5	G2	=VLOOKUP(E2,$A$2:$C$15,3,0)-SUMIFS($C$2:$C$15,$A$2:$A$15,E2,$B$2:$B$15,"JustPro")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Another option that might work, depending on your version

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Item Code	Seller	Price		Item Code	Position	Price Difference
2	FR11	ABCTech	16.49		FR11	3	1.38
3	FR11	KLG	15.19		GR44	3	3
4	FR11	JustPro	15.11		ZD16	2	0.1
5	FR11	MapLtd	14.99		PG20	1	0
6	GR44	KLG	38.99
7	GR44	ABCTech	36.49
8	GR44	JustPro	35.99
9	ZD16	KLG	12.99
10	ZD16	JustPro	12.89
11	ZD16	ABCTech	12.49
12	PG20	JustPro	22.79
13	PG20	ABCTech	21.99
14	PG20	MapLtd	19.99
15	PG20	KLG	18.49
16
Summary

Cell Formulas
Range	Formula
F2:F5	F2	=MATCH(E2&"JustPro",$A$2:$A$15&$B$2:$B$15,0)-MATCH(E2,$A$2:$A$15,0)+1
G2:G5	G2	=IF(F2=1,0,MAXIFS($C$2:$C$15,$A$2:$A$15,E2)-SUMIFS($C$2:$C$15,$A$2:$A$15,E2,$B$2:$B$15,"JustPro"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JumboCactuar]

Thanks both, will try these later

@Fluff using office365 will update when I get on desktop

 

[Fluff]

Ok, with 365 some other options, depending on which build your on.

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1	Item Code	Seller	Price		Item Code	Position	Price Difference	With Let
2	FR11	ABCTech	16.49		FR11	3	1.38	1.38
3	FR11	KLG	15.19		GR44	3	3	3
4	FR11	JustPro	15.11		ZD16	2	0.1	0.1
5	FR11	MapLtd	14.99		PG20	1	0	0
6	GR44	KLG	38.99
7	GR44	ABCTech	36.49
8	GR44	JustPro	35.99
9	ZD16	KLG	12.99
10	ZD16	JustPro	12.89
11	ZD16	ABCTech	12.49
12	PG20	JustPro	22.79
13	PG20	ABCTech	21.99
14	PG20	MapLtd	19.99
15	PG20	KLG	18.49
Summary

Cell Formulas
Range	Formula
F2:F5	F2	=XMATCH("JustPro",FILTER($B$2:$B$15,$A$2:$A$15=E2),0)
G2:G5	G2	=IF(F2=1,0,INDEX(FILTER($C$2:$C$15,$A$2:$A$15=E2),1)-INDEX(FILTER($C$2:$C$15,$A$2:$A$15=E2),F2))
H2:H5	H2	=LET(Fltr,FILTER($C$2:$C$15,$A$2:$A$15=E2),IF(F2=1,0,INDEX(Fltr,1)-INDEX(Fltr,F2)))

 

[JumboCactuar]

@Fluff
The XMATCH is slow calculating with 10k rows

Is there any VBA alternative?

 

[veyselemre]

Sub test()
    Dim sat&, sira&, i&, al$, eski$
    sat = 2
    eski = ""
    Range("d2:f" & Rows.Count).ClearContents
    With CreateObject("Scripting.Dictionary")
        For i = 2 To Cells(Rows.Count, 1).End(3).Row
            al = Cells(i, 1).Value
            If eski <> al Then
                If Not .exists(al) Then
                    Cells(sat, "d").Value = al
                    Cells(sat, "f").Value = Cells(i, 3).Value
                    .Item(al) = sat
                    sat = sat + 1
                End If
                sira = .Item(al)
                Cells(sira, "e").Value = Cells(sira, "e").Value + 1
                If Cells(i, 2).Value = "JustPro" Then
                    Cells(sira, "f").Value = Cells(sira, "f").Value - Cells(i, "c").Value
                    eski = al
                End If
            End If
        Next i
    End With
End Sub

 

[Fluff]

Another option

Sub JumboCactur()
   Dim Ary As Variant, Tmp As Variant
   Dim r As Long
   
   Ary = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            If Ary(r, 2) = "JustPro" Then
               .Add Ary(r, 1), Array(1, 0)
            Else
               .Add Ary(r, 1), Array(r, Ary(r, 3))
            End If
         ElseIf Ary(r, 2) = "JustPro" Then
            Tmp = .Item(Ary(r, 1))
            Tmp(0) = r - Tmp(0) + 1
            Tmp(1) = Tmp(1) - Ary(r, 3)
            .Item(Ary(r, 1)) = Tmp
         End If
      Next r
      Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
      Range("F2").Resize(.Count, 2).Value = Application.Index(.Items, 0)
   End With
End Sub

This will also create the list of Item codes in col E

 

[JumboCactuar]

Another option

Sub JumboCactur()
   Dim Ary As Variant, Tmp As Variant
   Dim r As Long
  
   Ary = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value2
  
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            If Ary(r, 2) = "JustPro" Then
               .Add Ary(r, 1), Array(1, 0)
            Else
               .Add Ary(r, 1), Array(r, Ary(r, 3))
            End If
         ElseIf Ary(r, 2) = "JustPro" Then
            Tmp = .Item(Ary(r, 1))
            Tmp(0) = r - Tmp(0) + 1
            Tmp(1) = Tmp(1) - Ary(r, 3)
            .Item(Ary(r, 1)) = Tmp
         End If
      Next r
      Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
      Range("F2").Resize(.Count, 2).Value = Application.Index(.Items, 0)
   End With
End Sub

This will also create the list of Item codes in col E

Thanks @Fluff Though with large dataset it comes out wrong
maybe if "JustPro" isn't found for an item which is in some cases - that could be breaking your code ?
if "JustPro" not found i just want to output 0 / 0 for column F/G

 

[Fluff]

How about

Sub JumboCactur()
   Dim Ary As Variant, Tmp As Variant
   Dim r As Long
   Dim Found As Boolean
   
   Ary = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            If Ary(r, 2) = "JustPro" Then
               Found = True
               .Add Ary(r, 1), Array(1, 0)
            Else
               .Add Ary(r, 1), Array(r, Ary(r, 3))
            End If
         ElseIf Ary(r, 2) = "JustPro" Then
            Found = True
            Tmp = .Item(Ary(r, 1))
            Tmp(0) = r - Tmp(0) + 1
            Tmp(1) = Tmp(1) - Ary(r, 3)
            .Item(Ary(r, 1)) = Tmp
         End If
         If r = UBound(Ary) Then
            If Not Found Then .Item(Ary(r, 1)) = Array(0, 0)
         ElseIf Ary(r, 1) <> Ary(r + 1, 1) Then
            If Not Found Then .Item(Ary(r, 1)) = Array(0, 0)
            Found = False
         End If
      Next r
      Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
      Range("F2").Resize(.Count, 2).Value = Application.Index(.Items, 0)
   End With
End Sub