Compare lists and generate new list with what is different from the first list

[bh24524]

Hello, as I mentioned last week I would be posting this. This is very similar to my post last week about comparing two lists and generating a third list of items that were missing. Well this time, I am looking to compare two lists and generate a third list of what is different from List 1. Periodically, we do audits on employee information between two systems to make sure they match. There are actually two different audits that we do but if I can get a solution for this one, then I can apply that the second one which has different fields, but the same number of comparisons.

SCENARIO

	A	B	C	D
1	List 1
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305275​	$ 20.00
4	John Smith 2	2345678​	10305276​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.00
7	John Smith 5	5678901​	10305276​	$ 21.00

	F	G	H	I
1	List 2
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.50
7	John Smith 5	5678901​	10305276​	$ 21.00

	K	L	M	N
1	List 3
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 4	4567890​	10305275​	$ 20.50

We are doing an audit between List 1 and 2 and looking for differences in position codes and/or pay rates. List 3 has the info of those who have something different between the two lists and lists them accordingly. John Smith1 has a different position code and a different pay rate. John Smith2 has a different position code but his pay rate is the same. John Smith4 has the same position code between the two lists but a different pay rate. So their names would be generated because one or BOTH of those fields has something different from the first list.

I'm pretty sure the formula here would utilize Aggregate but that is a function I am not at all familiar with so I definitely need help on that.

 

[Oddball2020]

Hello, as I mentioned last week I would be posting this. This is very similar to my post last week about comparing two lists and generating a third list of items that were missing. Well this time, I am looking to compare two lists and generate a third list of what is different from List 1. Periodically, we do audits on employee information between two systems to make sure they match. There are actually two different audits that we do but if I can get a solution for this one, then I can apply that the second one which has different fields, but the same number of comparisons.

SCENARIO

	A	B	C	D
1	List 1
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305275​	$ 20.00
4	John Smith 2	2345678​	10305276​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.00
7	John Smith 5	5678901​	10305276​	$ 21.00

	F	G	H	I
1	List 2
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.50
7	John Smith 5	5678901​	10305276​	$ 21.00

	K	L	M	N
1	List 3
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 4	4567890​	10305275​	$ 20.50

We are doing an audit between List 1 and 2 and looking for differences in position codes and/or pay rates. List 3 has the info of those who have something different between the two lists and lists them accordingly. John Smith1 has a different position code and a different pay rate. John Smith2 has a different position code but his pay rate is the same. John Smith4 has the same position code between the two lists but a different pay rate. So their names would be generated because one or BOTH of those fields has something different from the first list.

I'm pretty sure the formula here would utilize Aggregate but that is a function I am not at all familiar with so I definitely need help on that.

Hey, just a few quick questions for your situation:
-How would you like to go about it, on the sheet-side using formula, or on the VBA side?
- Are List 1 & List 2 in different worksheets in the same workbook? Or above/under each other on the same sheet?
- Do you own the workbook and can add additional columns to it?
- Is one of the Lists the "master" and the other is being compared to it, or they both need to be backward compared? ("generate a third list of what is different from List 1." sounds like List 1 is the master and List 2 is being compared to it, generated any differences as List 3 correct?)

My thoughts,
- If you own the workbook, then adding "flagging" columns to put IF THEN formulas with comparison conditions is an easy and straightforward method to "flag" any differences. Then filtering for those flags and dumping those on a new List 3 is easy. This can be done in VBA.
- VBA could also just do the comparison completely on the code side as well, but it would involve loops of comparison for each pertinent column, which if you have long lists, using the above approach may actually be easier/simpler to deal with, and less cumbersome to tweak visible formulas for other users than requiring coding edits in VBA.

Just curious what your situation is, before recommending a suitable answer, thanks!

 

[bh24524]

Hi, so actually whatever would be easier. List 1 could be considered the "Master List" and so whatever is in list 2 and is different from List 1 should be generated in List 3. Both lists actually come from different different spreadsheets, but they would be pasted into the same spreadsheet on the same tab. That spreadsheet will basically act as an audit template that allows me to do both of my sets of audits when I need to do it.

Here is a link to the other post I had posted that's kind of related to this one if it would help any.

Compare two lists and generate a new list of items missing from one list

Hello, so I learned how to do this in Google Sheets the other day. It was as the title says a comparison between two lists that I wanted a formula to list all the items from the first list that were NOT contained on the second list. I know you can do this a different way utilizing conditional...

www.mrexcel.com

 

[Oddball2020]

Hi, so actually whatever would be easier. List 1 could be considered the "Master List" and so whatever is in list 2 and is different from List 1 should be generated in List 3. Both lists actually come from different different spreadsheets, but they would be pasted into the same spreadsheet on the same tab. That spreadsheet will basically act as an audit template that allows me to do both of my sets of audits when I need to do it.

Here is a link to the other post I had posted that's kind of related to this one if it would help any.

Compare two lists and generate a new list of items missing from one list

Hello, so I learned how to do this in Google Sheets the other day. It was as the title says a comparison between two lists that I wanted a formula to list all the items from the first list that were NOT contained on the second list. I know you can do this a different way utilizing conditional...

www.mrexcel.com

So assuming the List 2 Names and Employee ID always match List 1 (so there's no possible disconnects there), one solution is to use VBA to input these two formulas (applied down the length of your names) in your Columns H & I:

Col H: =IF(VLOOKUP($K4,$F:$I,3,0)<>VLOOKUP($A4,$A:$D,3,0),VLOOKUP($F4,$F:$I,3,0),"")
Col I: =IF(VLOOKUP($K4,$F:$I,4,0)<>VLOOKUP($A4,$A:$D,4,0),VLOOKUP($F4,$F:$I,4,0),"")

But on another sheet, then filter for anything that isn't blank under Position Code or Rate of Pay and copy those into your List 3 on your main page, so it presents itself to you as the disconnects.

(Also noted that your List 3 is showing what's incorrectly displayed between List 2 & 1, not what that data entry should be according to List 1)

 

[bh24524]

Thinking more on this, I'm really not familiar with VBA, so I think maybe I'd rather just go with a formula instead. If I need to simplify this and limit it to just one field to audit at a time, I'm fine with that. So first paste the position code from both lists, and then when I've gotten that data, then paste the rates of pay.

 

[bh24524]

Tried replicating my other post referred to here and thought I had it but still working....

 

[bh24524]

Okay so here is what I came up with:

I had to exclude names as part of the formulas because I found that the two lists had names differently. One list had them as first then last name and the other had them as Last name then First.
I am trying to get it to list what is different in columns L thru N. I have an Index/Match formula in K to lookup name from column F based on ID number in L. I did hit Ctrl+shift+enter as you can see in screenshot for column L. I simply dragged that formula over to M and N, Here are my formulas used in L thru N:


Column L:

=IFERROR(INDEX(G$3:G$498,AGGREGATE(15,6,(ROW($H$3:$H$498)-ROW($H$3)+1)/(ISNA(MATCH($H$3:$H$498&"|"&$I$3:$I$498,$C$3:$C$499&"|"&$D$3:$D$499,0)))/($H$3:$H$498<>""),ROWS(L$3:L3))),"")

Column M:

=IFERROR(INDEX(H$3:H$498,AGGREGATE(15,6,(ROW($H$3:$H$498)-ROW($H$3)+1)/(ISNA(MATCH($H$3:$H$498&"|"&$I$3:$I$498,$C$3:$C$499&"|"&$D$3:$D$499,0)))/($H$3:$H$498<>""),ROWS(M$3:M3))),"")

Column N:

=IFERROR(INDEX(I$3:I$498,AGGREGATE(15,6,(ROW($H$3:$H$498)-ROW($H$3)+1)/(ISNA(MATCH($H$3:$H$498&"|"&$I$3:$I$498,$C$3:$C$499&"|"&$D$3:$D$499,0)))/($H$3:$H$498<>""),ROWS(N$3:N3))),"")

I dragged those down to subsequent rows and found it only listed one entry that had a difference but I also did a Conditional formatting audit to make sure it was only the one, but it turns out that there are several per below screenshots:

Just not sure what went wrong here but if someone is able to take a look? Here is the actual data pasted:

Online List

Database

Variance List

Name

ID

Position

Comp Rate

Name

ID

Position

Comp Rate

Name

ID

Position

Comp Rate

John 1

1628807​

20209586​

$ 25.47

John 1

1628807​

20209586​

$ 25.47

John 134

1787495​

10305275​

$ 23.10

John 2

1628813​

10305275​

$ 25.17

John 2

1628813​

10305275​

$ 25.17

John 3

1628824​

20209586​

$ 25.47

John 3

1628824​

20209586​

$ 25.47

John 4

1628826​

20209586​

$ 25.47

John 4

1628826​

20209586​

$ 25.47

John 5

1628830​

20209586​

$ 25.37

John 5

1628830​

20209586​

$ 25.37

John 6

1628870​

20209586​

$ 25.37

John 6

1628870​

20209586​

$ 25.37

John 7

1628890​

10305275​

$ 25.17

John 7

1628890​

10305275​

$ 25.17

John 8

1628898​

10305276​

$ 25.17

John 8

1628898​

10305276​

$ 25.17

John 9

1628906​

10305276​

$ 25.17

John 9

1628906​

10305276​

$ 25.17

John 10

1628907​

10305276​

$ 25.17

John 10

1628907​

10305276​

$ 25.17

John 11

1628911​

10305276​

$ 25.27

John 11

1628911​

10305276​

$ 25.27

John 12

1628918​

10305276​

$ 25.47

John 12

1628918​

10305276​

$ 25.47

John 13

1628920​

20209586​

$ 25.37

John 13

1628920​

20209586​

$ 25.37

John 14

1628930​

10305276​

$ 25.17

John 14

1628930​

10305276​

$ 25.17

John 15

1628933​

10305276​

$ 25.27

John 15

1628933​

10305276​

$ 25.27

John 16

1628946​

10305275​

$ 25.17

John 16

1628946​

10305275​

$ 25.17

John 17

1628959​

20209586​

$ 25.37

John 17

1628959​

20209586​

$ 25.37

John 18

1628973​

10305318​

$ 24.40

John 18

1628973​

10305318​

$ 24.40

John 19

1628975​

20209586​

$ 25.62

John 19

1628975​

20209586​

$ 25.62

John 20

1628978​

20209586​

$ 25.47

John 20

1628978​

20209586​

$ 25.47

John 21

1628991​

10305275​

$ 25.47

John 21

1628991​

10305275​

$ 25.47

John 22

1628993​

10305318​

$ 24.30

John 22

1628993​

10305318​

$ 24.30

John 23

1628994​

20209586​

$ 25.37

John 23

1628994​

20209586​

$ 25.37

John 24

1628996​

10305276​

$ 25.17

John 24

1628996​

10305276​

$ 25.17

John 25

1629016​

20209586​

$ 25.37

John 25

1629016​

20209586​

$ 25.37

John 26

1629018​

10305275​

$ 25.27

John 26

1629018​

10305275​

$ 25.27

John 27

1629020​

20209586​

$ 25.47

John 27

1629020​

20209586​

$ 25.47

John 28

1629022​

20209586​

$ 25.47

John 28

1629022​

20209586​

$ 25.47

John 29

1629030​

10305318​

$ 24.30

John 29

1629030​

10305318​

$ 24.30

John 30

1629033​

10305276​

$ 25.47

John 30

1629033​

10305276​

$ 25.47

John 31

1629051​

20209586​

$ 25.37

John 31

1629051​

10305275​

$ 25.17

John 32

1629065​

10305275​

$ 25.17

John 32

1629065​

10305275​

$ 25.17

John 33

1629085​

10305276​

$ 25.17

John 33

1629085​

10305276​

$ 25.17

John 34

1629088​

20209586​

$ 25.47

John 34

1629088​

20209586​

$ 25.47

John 35

1629100​

10305318​

$ 24.20

John 35

1629100​

10305318​

$ 24.20

John 36

1629101​

10305275​

$ 25.17

John 37

1629120​

10305276​

$ 25.27

John 37

1629120​

10305276​

$ 25.27

John 38

1629128​

10305318​

$ 24.40

John 38

1629128​

10305318​

$ 24.40

John 39

1629147​

20209586​

$ 25.37

John 39

1629147​

20209586​

$ 25.37

John 40

1629149​

20209586​

$ 25.37

John 40

1629149​

20209586​

$ 25.37

John 41

1629201​

10305276​

$ 25.17

John 41

1629201​

10305276​

$ 25.17

John 42

1629205​

20209586​

$ 25.37

John 42

1629205​

20209586​

$ 25.37

John 43

1629207​

10305318​

$ 24.10

John 43

1629207​

10305318​

$ 24.10

John 44

1629210​

10305276​

$ 25.47

John 44

1629210​

10305276​

$ 25.47

John 45

1629213​

10305276​

$ 25.62

John 45

1629213​

10305276​

$ 25.62

John 46

1629220​

20209586​

$ 25.37

John 46

1629220​

20209586​

$ 25.37

John 47

1629230​

20209586​

$ 25.37

John 47

1629230​

20209586​

$ 25.37

John 48

1629239​

10305318​

$ 24.55

John 48

1629239​

10305318​

$ 24.55

John 49

1629249​

20209586​

$ 25.47

John 49

1629249​

20209586​

$ 25.47

John 50

1629252​

20209586​

$ 25.47

John 50

1629252​

20209586​

$ 25.47

John 51

1629263​

10305318​

$ 24.30

John 51

1629263​

10305318​

$ 24.30

John 52

1629301​

20209586​

$ 25.37

John 52

1629301​

20209586​

$ 25.37

John 53

1629304​

10305275​

$ 25.17

John 53

1629304​

10305275​

$ 25.17

John 54

1629306​

10305275​

$ 25.27

John 54

1629306​

10305275​

$ 25.27

John 55

1629309​

10305275​

$ 25.47

John 55

1629309​

10305275​

$ 25.47

John 56

1629317​

10305276​

$ 25.17

John 56

1629317​

10305276​

$ 25.17

John 57

1629319​

10305276​

$ 25.47

John 57

1629319​

10305276​

$ 25.47

John 58

1629325​

10305275​

$ 25.47

John 58

1629325​

10305275​

$ 25.47

John 59

1629347​

10305318​

$ 24.30

John 59

1629347​

10305318​

$ 24.30

John 60

1629348​

10305318​

$ 24.10

John 60

1629348​

10305318​

$ 24.10

John 61

1629349​

10305318​

$ 24.40

John 61

1629349​

10305318​

$ 24.40

John 62

1629351​

10305276​

$ 25.47

John 62

1629351​

10305276​

$ 25.47

John 63

1629354​

10305275​

$ 25.47

John 63

1629354​

10305275​

$ 25.47

John 64

1629364​

20209586​

$ 23.30

John 64

1629364​

20209586​

$ 23.30

John 65

1629378​

20209586​

$ 25.37

John 65

1629378​

20209586​

$ 25.37

John 66

1629381​

20209586​

$ 25.37

John 66

1629381​

20209586​

$ 25.37

John 67

1629397​

20209586​

$ 25.47

John 67

1629397​

20209586​

$ 25.47

John 68

1629398​

10305276​

$ 25.47

John 68

1629398​

10305276​

$ 25.47

John 69

1629439​

20209586​

$ 25.37

John 69

1629439​

20209586​

$ 25.37

John 70

1629443​

20209586​

$ 25.37

John 70

1629443​

20209586​

$ 25.37

John 71

1629463​

10305318​

$ 24.40

John 72

1629464​

10305275​

$ 25.17

John 72

1629464​

10305275​

$ 25.17

John 73

1629466​

20209586​

$ 25.37

John 73

1629466​

20209586​

$ 25.37

John 74

1629485​

10305276​

$ 25.47

John 74

1629485​

10305276​

$ 25.47

John 75

1629503​

10305276​

$ 25.47

John 75

1629503​

10305276​

$ 25.47

John 76

1629515​

10305276​

$ 25.17

John 76

1629515​

10305276​

$ 25.17

John 77

1629516​

20209586​

$ 25.37

John 77

1629516​

20209586​

$ 25.37

John 78

1629522​

20209586​

$ 25.37

John 78

1629522​

20209586​

$ 25.37

John 79

1629524​

10305276​

$ 25.17

John 79

1629524​

10305276​

$ 25.17

John 80

1629526​

20209586​

$ 25.47

John 80

1629526​

20209586​

$ 25.47

John 81

1629534​

20209586​

$ 25.47

John 81

1629534​

20209586​

$ 25.47

John 82

1629543​

20209586​

$ 25.47

John 82

1629543​

20209586​

$ 25.47

John 83

1629549​

10305275​

$ 25.47

John 83

1629549​

10305275​

$ 25.47

John 84

1629578​

20209586​

$ 25.47

John 84

1629578​

20209586​

$ 25.47

John 85

1629582​

20209586​

$ 25.37

John 85

1629582​

20209586​

$ 25.37

John 86

1629594​

10305275​

$ 25.47

John 86

1629594​

10305275​

$ 25.47

John 87

1629619​

10305275​

$ 25.27

John 87

1629619​

10305275​

$ 25.27

John 88

1629620​

20209586​

$ 24.32

John 88

1629620​

John 89

1629644​

20209586​

$ 25.37

John 89

1629644​

20209586​

$ 25.37

John 90

1629694​

20209586​

$ 25.47

John 90

1629694​

20209586​

$ 25.47

John 91

1629696​

10305276​

$ 25.47

John 91

1629696​

10305276​

$ 25.47

John 92

1629707​

10305276​

$ 25.47

John 92

1629707​

10305276​

$ 25.47

John 93

1629713​

10305276​

$ 25.27

John 93

1629713​

10305276​

$ 25.27

John 94

1629724​

20209586​

$ 25.47

John 94

1629724​

20209586​

$ 25.47

John 95

1629734​

10305276​

$ 25.47

John 95

1629734​

10305276​

$ 25.47

John 96

1629736​

10305318​

$ 24.40

John 96

1629736​

10305318​

$ 24.40

John 97

1629740​

10305318​

$ 24.40

John 97

1629740​

10305318​

$ 24.40

John 98

1629773​

10305275​

$ 25.17

John 98

1629773​

10305275​

$ 25.17

John 99

1629774​

10305276​

$ 25.47

John 99

1629774​

10305276​

$ 25.47

John 100

1629781​

20209586​

$ 25.37

John 100

1629781​

20209586​

$ 25.37

John 101

1629797​

10305276​

$ 25.17

John 101

1629797​

10305276​

$ 25.17

John 102

1629798​

10305318​

$ 24.30

John 102

1629798​

10305318​

$ 24.30

John 103

1629818​

10305276​

$ 25.17

John 103

1629818​

10305276​

$ 25.17

John 104

1629819​

10305275​

$ 25.47

John 104

1629819​

10305275​

$ 25.47

John 105

1629820​

10305276​

$ 25.47

John 105

1629820​

10305276​

$ 25.47

John 106

1629837​

20209586​

$ 25.47

John 106

1629837​

20209586​

$ 25.47

John 107

1629838​

20209586​

$ 25.37

John 107

1629838​

20209586​

$ 25.37

John 108

1629844​

10305276​

$ 25.47

John 108

1629844​

10305276​

$ 25.47

John 109

1629852​

10305275​

$ 25.27

John 109

1629852​

10305275​

$ 25.27

John 110

1629853​

10305276​

$ 25.62

John 110

1629853​

10305276​

$ 25.62

John 111

1629859​

20209586​

$ 25.37

John 111

1629859​

20209586​

$ 25.37

John 112

1629880​

10305275​

$ 25.47

John 112

1629880​

10305275​

$ 25.47

John 113

1629906​

10305275​

$ 25.17

John 113

1629906​

10305275​

$ 25.17

John 114

1629908​

10305275​

$ 25.27

John 114

1629908​

10305275​

$ 25.27

John 115

1629909​

10305276​

$ 25.27

John 115

1629909​

10305276​

$ 25.27

John 116

1629911​

10305276​

$ 25.47

John 116

1629911​

10305276​

$ 25.47

John 117

1629912​

20209586​

$ 25.62

John 117

1629912​

20209586​

$ 25.62

John 118

1629915​

20209586​

$ 25.62

John 118

1629915​

20209586​

$ 25.62

John 119

1629923​

10305276​

$ 25.17

John 119

1629923​

10305276​

$ 25.17

John 120

1629933​

10305276​

$ 25.62

John 120

1629933​

10305276​

$ 25.62

John 121

1629936​

20209586​

$ 25.47

John 121

1629936​

20209586​

$ 25.47

John 122

1629947​

20209586​

$ 25.37

John 122

1629947​

20209586​

$ 25.37

John 123

1629973​

10305318​

$ 24.30

John 123

1629973​

10305318​

$ 24.30

John 124

1629974​

20209586​

$ 25.47

John 124

1629974​

20209586​

$ 25.47

John 125

1629976​

10305318​

$ 24.55

John 125

1629976​

10305318​

$ 24.55

John 126

1629977​

20209586​

$ 25.62

John 126

1629977​

20209586​

$ 25.62

John 127

1630002​

10305275​

$ 23.40

John 127

1630002​

10305275​

$ 23.40

John 128

1630009​

10305275​

$ 22.00

John 128

1630009​

10305275​

$ 22.00

John 129

1630012​

10305276​

$ 25.62

John 129

1630012​

10305276​

$ 25.62

John 130

1630030​

10305275​

$ 25.47

John 130

1630030​

10305275​

$ 25.47

John 131

1735708​

10305275​

$ 23.40

John 131

1735708​

10305275​

$ 23.40

John 132

1763325​

10305276​

$ 23.10

John 132

1763325​

10305276​

$ 23.10

John 133

1785699​

10305275​

$ 23.40

John 133

1785699​

10305275​

$ 23.40

John 134

1787495​

10305275​

$ 22.50

John 134

1787495​

10305275​

$ 23.10

John 135

1794468​

10305275​

$ 22.70

John 135

1794468​

10305275​

$ 22.70

John 136

1794924​

10305275​

$ 22.25

John 136

1794924​

10305275​

$ 22.25

John 137

1795573​

10305275​

$ 20.50

John 137

1795573​

10305275​

$ 20.50

John 138

1795768​

10305276​

$ 25.17

John 138

1795768​

10305276​

$ 25.17

John 139

3216125​

10305275​

$ 22.60

John 139

3216125​

10305275​

$ 22.60

John 140

3272646​

10305275​

$ 22.15

John 140

3272646​

10305275​

$ 22.15

John 141

3291772​

20209586​

$ 22.35

John 141

3291772​

20209586​

$ 22.35

John 142

3320432​

10305275​

$ 22.15

John 142

3320432​

10305275​

$ 22.15

John 143

3327640​

10305275​

$ 22.15

John 143

3327640​

10305275​

$ 22.15

John 144

3345370​

10305275​

$ 22.00

John 144

3345370​

10305275​

$ 22.00

John 145

3372749​

20209586​

$ 21.90

John 145

3372749​

20209586​

$ 21.90

John 146

3380525​

10305275​

$ 21.70

John 146

3380525​

10305275​

$ 21.70

John 147

3383234​

10305275​

$ 21.70

John 147

3383234​

10305275​

$ 21.70

John 148

3392315​

10305276​

$ 21.70

John 148

3392315​

10305276​

$ 21.70

John 149

3392321​

10305276​

$ 21.80

John 149

3392321​

10305276​

$ 21.80

John 150

3392323​

10305275​

$ 22.00

John 150

3392323​

10305275​

$ 22.00

John 151

3394603​

10305275​

$ 21.70

John 151

3394603​

10305275​

$ 21.70

John 152

3394606​

10305275​

$ 21.70

John 152

3394606​

10305275​

$ 21.70

John 153

3430064​

10305275​

$ 21.70

John 153

3430064​

10305275​

$ 21.70

John 154

3431962​

10305275​

$ 21.80

John 154

3431962​

10305275​

$ 21.80

John 155

3431964​

10305275​

$ 21.80

John 155

3431964​

10305275​

$ 21.80

John 156

3438399​

10305275​

$ 22.00

John 156

3438399​

10305275​

$ 22.00

John 157

3440391​

10305275​

$ 21.70

John 157

3440391​

10305275​

$ 21.70

John 158

3446835​

10305275​

$ 21.70

John 158

3446835​

10305275​

$ 21.70

John 159

3459452​

10305275​

$ 21.80

John 159

3459452​

10305275​

$ 21.80

John 160

3459458​

10305275​

$ 21.70

John 160

3459458​

10305275​

$ 21.70

John 161

3465229​

10305275​

$ 21.40

John 161

3465229​

10305275​

$ 21.40

John 162

3469985​

10305275​

$ 21.10

John 162

3469985​

10305275​

$ 21.10

John 163

3471973​

10305275​

$ 21.10

John 163

3471973​

10305275​

$ 21.10

John 164

3480147​

10305275​

$ 21.10

John 164

3480147​

10305275​

$ 21.10

John 165

3480844​

10305275​

$ 21.40

John 165

3480844​

10305275​

$ 21.40

John 166

3481404​

10305275​

$ 21.40

John 166

3481404​

10305275​

$ 21.40

John 167

3499964​

10305275​

$ 21.10

John 167

3499964​

10305275​

$ 21.10

John 168

3500958​

10305276​

$ 21.40

John 168

3500958​

10305276​

$ 21.40

John 169

3521114​

10305275​

$ 21.20

John 169

3521114​

10305275​

$ 21.20

John 170

3535082​

10305275​

$ 20.80

John 170

3535082​

10305275​

$ 20.80

John 171

3541418​

10305275​

$ 20.60

John 171

3541418​

10305275​

$ 20.60

John 172

3543447​

10305275​

$ 20.80

John 172

3543447​

10305275​

$ 20.80

John 173

3543819​

10305275​

$ 20.60

John 173

3543819​

10305275​

$ 20.60

John 174

3545345​

10305275​

$ 20.50

John 174

3545345​

10305275​

$ 20.50

John 175

3545348​

10305275​

$ 20.80

John 175

3545348​

10305275​

$ 20.80

John 176

3546242​

10305275​

$ 20.50

John 176

3546242​

10305275​

$ 20.50

John 177

3546356​

10305275​

$ 20.80

John 177

3546356​

10305275​

$ 20.80

John 178

3549481​

10305275​

$ 20.80

John 178

3549481​

10305275​

$ 20.80

John 179

3549730​

10305275​

$ 20.50

John 179

3549730​

10305275​

$ 20.50

John 180

3549774​

10305275​

$ 20.60

John 180

3549774​

10305275​

$ 20.60

John 181

3550177​

10305275​

$ 20.60

John 181

3550177​

10305275​

$ 20.60

John 182

3550622​

10305275​

$ 20.50

John 182

3550622​

10305275​

$ 20.50

John 183

3554610​

10305275​

$ 20.50

John 183

3554610​

10305275​

$ 20.50

John 184

3556857​

10305275​

$ 20.80

John 184

3556857​

10305275​

$ 20.80

John 185

3561705​

10305275​

$ 20.80

John 185

3561705​

10305275​

$ 20.80

John 186

3561726​

10305275​

$ 20.80

John 186

3561726​

10305275​

$ 20.80

John 187

3563284​

10305275​

$ 20.80

John 187

3563284​

10305275​

$ 20.80

John 188

3625582​

10305275​

$ 19.50

John 188

3625582​

10305275​

$ 19.50

John 189

3632078​

10305275​

$ 19.80

John 189

3632078​

10305275​

$ 19.80

John 190

3653210​

10305275​

$ 19.80

John 190

3653210​

10305275​

$ 19.80

John 191

3656886​

10305275​

$ 19.80

John 191

3656886​

10305275​

$ 19.80

John 192

3667595​

10305275​

$ 17.50

John 192

3667595​

10305275​

$ 17.50

John 193

3677929​

10305275​

$ 17.80

John 193

3677929​

10305275​

$ 17.80

John 194

3681482​

10305275​

$ 17.50

John 194

3681482​

10305275​

$ 17.50

John 195

3691864​

10305275​

$ 15.80

John 195

3691864​

10305275​

$ 17.80

John 196

3694594​

10305275​

$ 17.80

John 196

3694594​

10305275​

$ 17.80

John 197

3694986​

10305275​

$ 15.60

John 197

3694986​

10305275​

$ 15.60

John 198

3702267​

10305275​

$ 15.80

John 198

3702267​

10305275​

$ 15.80

John 199

3706928​

10305275​

$ 15.80

John 199

3706928​

10305275​

$ 15.80

John 200

3712565​

10305276​

$ 15.80

John 200

3712565​

10305276​

$ 15.80

John 201

3717320​

10305275​

$ 15.30

John 201

3717320​

10305275​

$ 15.80

John 202

3717838​

10305275​

$ 15.50

John 202

3717838​

10305275​

$ 15.50

John 203

3727120​

10305276​

$ 15.30

John 203

3727120​

10305276​

$ 15.30

John 204

3735462​

10305275​

$ 15.00

John 204

3735462​

10305275​

$ 15.00

John 205

3738461​

10305275​

$ 15.10

John 205

3738461​

10305276​

$ 15.30

John 206

3738812​

10305276​

$ 15.10

John 206

3738812​

10305276​

$ 15.10

John 207

3739429​

10305276​

$ 15.30

John 207

3739429​

10305276​

$ 15.30

John 208

3739924​

10305276​

$ 15.30

John 208

3739924​

10305276​

$ 15.30

John 209

3744012​

10305275​

$ 15.00

John 209

3744012​

10305275​

$ 15.00

John 210

3744664​

10305276​

$ 15.00

John 210

3744664​

10305276​

$ 15.00

John 211

3745272​

10305275​

$ 15.00

John 211

3745272​

10305275​

$ 15.00

John 212

3747513​

10305275​

$ 15.00

John 212

3747513​

10305275​

$ 15.00

John 213

3749671​

10305275​

$ 15.10

John 213

3749671​

10305275​

$ 15.10

John 214

3752006​

10305276​

$ 15.00

John 214

3752006​

10305276​

$ 15.00

John 215

3752007​

10305276​

$ 15.00

John 215

3752007​

10305276​

$ 15.00

John 216

3752286​

10305276​

$ 15.30

John 216

3752286​

10305276​

$ 15.30

John 217

3752293​

10305276​

$ 15.00

John 217

3752293​

10305276​

$ 15.00

John 218

3752295​

10305276​

$ 15.30

John 218

3752295​

10305276​

$ 15.30

John 219

3752297​

10305276​

$ 15.00

John 219

3752297​

10305276​

$ 15.00

John 220

3752298​

10305276​

$ 15.00

John 220

3752298​

10305276​

$ 15.00

John 221

3752892​

10305276​

$ 15.30

John 221

3752892​

10305276​

$ 15.30

John 222

3752893​

10305276​

$ 15.30

John 222

3752893​

10305276​

$ 15.30

John 223

3752894​

10305276​

$ 15.30

John 223

3752894​

10305276​

$ 15.30

John 224

3752897​

10305276​

$ 15.00

John 224

3752897​

10305276​

$ 15.00

John 225

3753337​

10305276​

$ 15.00

John 225

3753337​

10305276​

$ 15.00

John 226

3753341​

10305276​

$ 15.00

John 226

3753341​

10305276​

$ 15.00

John 227

3753342​

10305276​

$ 15.30

John 227

3753342​

10305276​

$ 15.30

John 228

3754176​

10305276​

$ 15.00

John 228

3754176​

10305276​

$ 15.00

John 229

3754729​

10305276​

$ 15.00

John 229

3754729​

10305276​

$ 15.00

John 230

3754730​

10305276​

$ 15.00

John 230

3754730​

10305276​

$ 15.00

John 231

3755499​

10305276​

$ 15.00

John 231

3755499​

10305276​

$ 15.00

John 232

3755501​

10305276​

$ 15.00

John 232

3755501​

10305276​

$ 15.00

John 233

3756473​

10305276​

$ 15.30

John 233

3756473​

10305276​

$ 15.30

John 234

3756475​

10305276​

$ 15.00

John 234

3756475​

10305276​

$ 15.00

John 235

3756476​

10305276​

$ 15.00

John 235

3756476​

10305276​

$ 15.00

Sorry I know its a lot.

 

[Marc L]

Hi, according to your last attachment and to Excel basics a VBA demonstration as a beginner starter :​

Sub Demo1()
  Const D = "&""¤""&"
    Dim A$
        Application.ScreenUpdating = False
        If Not IsEmpty([A2]) Then Rows(2).Insert
    With [A3].CurrentRegion.Columns
        A = .Item(1).Address & D & .Item(2).Address & D & .Item(3).Address & D & .Item(4).Address & ",0)))"
    End With
    With [F3].CurrentRegion.Columns
        A = .Item(1).Address & D & .Item(2).Address & D & .Item(3).Address & D & .Item(4).Address & "," & A
       .Item(5).Value2 = Evaluate("IF({1},ISERROR(MATCH(" & A)
        [J2].Formula = "=J4"
       .Resize(, 5).AdvancedFilter 2, [J1:J2], [K3:N3]
        Union(.Item(5), [J2]).Clear
    End With
        Application.ScreenUpdating = True
End Sub

 

[bh24524]

Hi, according to your last attachment and to Excel basics a VBA demonstration as a beginner starter :​

Sub Demo1()
  Const D = "&""¤""&"
    Dim A$
        Application.ScreenUpdating = False
        If Not IsEmpty([A2]) Then Rows(2).Insert
    With [A3].CurrentRegion.Columns
        A = .Item(1).Address & D & .Item(2).Address & D & .Item(3).Address & D & .Item(4).Address & ",0)))"
    End With
    With [F3].CurrentRegion.Columns
        A = .Item(1).Address & D & .Item(2).Address & D & .Item(3).Address & D & .Item(4).Address & "," & A
       .Item(5).Value2 = Evaluate("IF({1},ISERROR(MATCH(" & A)
        [J2].Formula = "=J4"
       .Resize(, 5).AdvancedFilter 2, [J1:J2], [K3:N3]
        Union(.Item(5), [J2]).Clear
    End With
        Application.ScreenUpdating = True
End Sub

So what do I do with this?

 

[bh24524]

Still looking for help on this. Can't figure out these solutions above. Any help?