Compare columns match and insert data from another column [Closed]

Report
Posts
1
Registration date
Sunday April 21, 2013
Status
Member
Last seen
April 21, 2013
-
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
-
Hello,

have a sheet with following info

A B C D E F G H I J


date-sum-item percentage- date sum item

now i need to match the date sum and item in columns A B C with EFG and if there is a match report percentage from Column D. tried VLOOKUP and MATCH but wasnt sucessful can you help out please?

thanks a lot

Martin

12 replies

Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
OK Mfrendo, let's give this a try.

I have copied the first part and pasted it to the range A1:F189.
Row 1 is header and column F with header FULL is empty.

Then I pasted the second part to the range H1:M191.

After running the code below, when a match is found (comparing part 2 with part 1) percentage is filled in column N.

Here is the code:
Sub CompareToAddPercentage()
Dim x As Integer, cell As Range
For Each cell In Range("H2:H191")
    x = 1
    Do
        x = x + 1
        If cell & cell.Offset(0, 4) & cell.Offset(0, 3) = _
            Range("A" & x) & Range("B" & x) & Range("D" & x) Then
            cell.Offset(0, 6) = cell.Offset(0, -3)
        End If
    Loop Until x = 189
Next cell
End Sub

Hopefully we are getting close to the solution.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
@Mfrendo

Is it possible to upload a sample file with more details

Thanks

Patnaik
tried to import file but so far didnt manage how to upload file here
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
HI

Please try the below mention links
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Hello,

I think Patnaik forgot to mention the links.

Try www.speedyshare.com Mfrendo.
After uploading you will receive a download link which you can post back.

Best regards,
Trowa
@Towad

Hi

I always paste the links for uploading of sample file but they are not appear i think they are stucking in SPAM may be if you can then please check

and also i post a new thread for POST SAMPLE WORKBOOK in Member Forum but i can't see that
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Hi Patnaik,

Trying to answer a second time (apparently got deleted as well).

When I suspected a blacklisted word or phrase I contacted Bionik, who confirmed it for me.

Best regards,
Trowa
this is the best i can do bascially the data is on one sheet in various columns. the first part below includes % while the second part beneath the first part is without the % figures. now i need to match the second part with the first part . and where the dates and seg/flt are the same then copy the % from the first part and show on the second part. i can also split both parts in 2 sheets

first part

DATE DAY FLT SEC % FULL Fdate Month Year
01/05/2013 WED 306 MLAMUC 85 01/05/2013 May 2013 MLADUS 352 WED
01/05/2013 WED 307 MUCMLA 81 01/05/2013 May 2013 MLAHAM 368 WED
01/05/2013 WED 328 MLAFRA 70 01/05/2013 May 2013 MLATXL 376 WED
01/05/2013 WED 329 FRAMLA 75 01/05/2013 May 2013 TXLMLA 377 WED
01/05/2013 WED 352 MLADUS 53 02/05/2013 May 2013 CTAMUC 1308 THURS
01/05/2013 WED 353 DUSMLA 103 02/05/2013 May 2013 DUSMLA 353 THURS
01/05/2013 WED 368 MLAHAM 63 02/05/2013 May 2013 FRAMLA 329 THURS
01/05/2013 WED 369 HAMMLA 96 02/05/2013 May 2013 MLADUS 352 THURS
01/05/2013 WED 376 MLATXL 79 02/05/2013 May 2013 MLAFRA 328 THURS
01/05/2013 WED 377 TXLMLA 78 02/05/2013 May 2013 MLAMUC 306 THURS
01/05/2013 WED 1308TAMUC 80 02/05/2013

second part
02/05/2013 May 2013 MUCMLA 307 THUR
03/05/2013 May 2013 DUSMLA 353 FRI
03/05/2013 May 2013 FRAMLA 329 FRI
03/05/2013 May 2013 FRAMLA 329 FRI
03/05/2013 May 2013 MLADUS 352 FRI
03/05/2013 May 2013 MLAMUC 306 FRI
03/05/2013 May 2013 MLATXL 376 FRI
03/05/2013 May 2013 MUCMLA 307 FRI
03/05/2013 May 2013 TXLMLA 377 FRI
04/05/2013 May 2013 DUSMLA 353 SAT
04/05/2013 May 2013 FRAMLA 329 SAT
04/05/2013 May 2013 MUCCTA 1309 SAT
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Hi Mfrendo,

If I understand you correctly then this formula will work:
=IF(AND(EXACT(A1,E1),EXACT(B1,F1),EXACT(C1,G1)),D1,"")

ie.: IF A=E AND B=F AND C=G then value D else nothing.

Keep in mind that you probably have to format the formula column to display the percentages properly.

Best regards,
Trowa
hi Trowa thanks for the info but unfortunately it didnt function. i think we need to have a look up for the whole sheet and find a match and then quote the missing % from the required column on the the empty column
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
OK Mfrendo, I'm a little confused now.

Did you try www.speedyshare.com?

Does the second part come after or below the first part (Uploading your file will clearify a lot without explaining?

So from the first (first line/row) part we take Date, FLT and Sec:
01/05/2013, 306 and MLAMUC

And compare it with second part:
02/05/2013, 307 and MUCMLA

This way I can't find a match anywhere.

Best regards,
Trowa
hi Trowa thanks for your reply to recap here is the file that is on the same sheet. the first part is shown as follows

DATE FLT DEP SEC % FULL
12/05/2013 376 10:30 MLATXL 92
08/05/2013 376 14:55 MLATXL 58
12/05/2013 369 12:25 HAMMLA 70
16/05/2013 307 12:40 MUCMLA 70
04/05/2013 1308 17:00 CTAMUC 80
04/05/2013 1309 19:55 MUCCTA 52
03/05/2013 352 08:00 MLADUS 48
02/05/2013 306 09:25 MLAMUC 65
02/05/2013 352 16:00 MLADUS 68
01/05/2013 328 15:25 MLAFRA 70
12/05/2013 352 08:00 MLADUS 76
19/05/2013 352 08:00 MLADUS 68
27/05/2013 306 09:25 MLAMUC 66
01/05/2013 329 19:15 FRAMLA 75
20/05/2013 1309 19:55 MUCCTA 89
29/05/2013 307 12:40 MUCMLA 60
01/05/2013 1309 20:00 MUCCTA 84
08/05/2013 369 12:25 HAMMLA 87
15/05/2013 376 14:55 MLATXL 68
03/05/2013 376 09:30 MLATXL 66
16/05/2013 306 09:25 MLAMUC 50
03/05/2013 377 13:10 TXLMLA 55
18/05/2013 1308 17:00 CTAMUC 57
26/05/2013 328 15:25 MLAFRA 95
06/05/2013 328 15:25 MLAFRA 82
04/05/2013 306 09:25 MLAMUC 79
05/05/2013 376 10:30 MLATXL 90
07/05/2013 307 12:40 MUCMLA 57
12/05/2013 309 10:35 MUCMLA 41
01/05/2013 1308 17:00 CTAMUC 80
08/05/2013 307 12:40 MUCMLA 75
17/05/2013 353 11:40 DUSMLA 93
01/05/2013 376 14:55 MLATXL 79
04/05/2013 329 19:15 FRAMLA 51
06/05/2013 329 19:15 FRAMLA 62
08/05/2013 368 08:30 MLAHAM 72
12/05/2013 306 09:25 MLAMUC 88
13/05/2013 352 16:00 MLADUS 89
14/05/2013 307 12:40 MUCMLA 49
16/05/2013 328 15:25 MLAFRA 36
01/05/2013 369 12:25 HAMMLA 96
06/05/2013 352 16:00 MLADUS 83
07/05/2013 306 09:25 MLAMUC 58
10/05/2013 376 09:30 MLATXL 65
12/05/2013 377 14:10 TXLMLA 78
11/05/2013 307 12:40 MUCMLA 47
02/05/2013 353 19:35 DUSMLA 51
03/05/2013 306 09:25 MLAMUC 80
05/05/2013 306 09:25 MLAMUC 72
11/05/2013 328 15:25 MLAFRA 78
12/05/2013 368 08:30 MLAHAM 94
13/05/2013 329 19:15 FRAMLA 63
17/05/2013 377 13:10 TXLMLA 46
18/05/2013 306 09:25 MLAMUC 66
19/05/2013 306 09:25 MLAMUC 36
26/05/2013 352 08:00 MLADUS 66
01/05/2013 377 18:45 TXLMLA 78
07/05/2013 328 15:25 MLAFRA 52
07/05/2013 329 19:15 FRAMLA 57
11/05/2013 352 08:00 MLADUS 76
16/05/2013 329 19:15 FRAMLA 65
12/05/2013 353 11:40 DUSMLA 101
14/05/2013 329 19:15 FRAMLA 48
15/05/2013 306 09:25 MLAMUC 52
16/05/2013 1309 20:00 MUCCTA 84
01/05/2013 307 12:40 MUCMLA 81
05/05/2013 368 08:30 MLAHAM 74
06/05/2013 307 12:40 MUCMLA 62
06/05/2013 1309 19:55 MUCCTA 52
08/05/2013 306 09:25 MLAMUC 57
09/05/2013 306 09:25 MLAMUC 59
10/05/2013 352 08:00 MLADUS 68
11/05/2013 329 19:15 FRAMLA 55
15/05/2013 352 08:00 MLADUS 60
04/05/2013 353 11:40 DUSMLA 66
05/05/2013 308 07:30 MLAMUC 69
03/05/2013 329 19:15 FRAMLA 58
06/05/2013 1308 17:00 CTAMUC 70
15/05/2013 353 11:40 DUSMLA 81
17/05/2013 306 09:25 MLAMUC 53
17/05/2013 328 15:25 MLAFRA 52
01/05/2013 352 08:00 MLADUS 53
04/05/2013 307 12:40 MUCMLA 61
05/05/2013 353 11:40 DUSMLA 81
10/05/2013 328 15:25 MLAFRA 72
16/05/2013 352 16:00 MLADUS 58
16/05/2013 353 19:35 DUSMLA 63
19/05/2013 376 10:30 MLATXL 107
20/05/2013 328 15:25 MLAFRA 78
27/05/2013 352 16:00 MLADUS 74
28/05/2013 306 09:25 MLAMUC 105
06/05/2013 353 19:35 DUSMLA 57
09/05/2013 328 15:25 MLAFRA 68
13/05/2013 306 09:25 MLAMUC 88
13/05/2013 1308 17:00 CTAMUC 84
17/05/2013 352 08:00 MLADUS 78
02/05/2013 307 12:40 MUCMLA 76
02/05/2013 328 15:25 MLAFRA 70
05/05/2013 329 19:15 FRAMLA 45
05/05/2013 377 14:10 TXLMLA 78
06/05/2013 306 09:25 MLAMUC 66
11/05/2013 353 11:40 DUSMLA 71
15/05/2013 328 15:25 MLAFRA 51
19/05/2013 353 11:40 DUSMLA 108
27/05/2013 307 12:40 MUCMLA 66
04/05/2013 352 08:00 MLADUS 80
05/05/2013 309 10:35 MUCMLA 60
05/05/2013 352 08:00 MLADUS 75
09/05/2013 353 19:35 DUSMLA 73
10/05/2013 306 09:25 MLAMUC 65
12/05/2013 329 19:15 FRAMLA 55
18/05/2013 307 12:40 MUCMLA 96
14/05/2013 306 09:25 MLAMUC 57
15/05/2013 369 12:25 HAMMLA 52
15/05/2013 1308 17:00 CTAMUC 44
02/05/2013 1309 20:00 MUCCTA 71
08/05/2013 328 15:25 MLAFRA 42
08/05/2013 353 11:40 DUSMLA 84
09/05/2013 352 16:00 MLADUS 70
10/05/2013 329 19:15 FRAMLA 63
18/05/2013 328 15:25 MLAFRA 64
18/05/2013 329 19:15 FRAMLA 92
19/05/2013 377 14:10 TXLMLA 92
27/05/2013 353 19:35 DUSMLA 50
09/05/2013 307 12:40 MUCMLA 101
13/05/2013 353 19:35 DUSMLA 51
15/05/2013 307 12:40 MUCMLA 53
17/05/2013 307 12:40 MUCMLA 91
19/05/2013 308 07:30 MLAMUC 49
19/05/2013 328 15:25 MLAFRA 81
19/05/2013 369 12:25 HAMMLA 102
26/05/2013 368 08:30 MLAHAM 66
26/05/2013 376 10:30 MLATXL 105
27/05/2013 329 19:15 FRAMLA 42
01/05/2013 306 09:25 MLAMUC 85
01/05/2013 368 08:30 MLAHAM 63
02/05/2013 1308 17:00 CTAMUC 60
08/05/2013 329 19:15 FRAMLA 81
11/05/2013 306 09:25 MLAMUC 76
12/05/2013 308 07:30 MLAMUC 90
17/05/2013 329 19:15 FRAMLA 85
03/05/2013 328 15:25 MLAFRA 77
05/05/2013 307 12:40 MUCMLA 45
05/05/2013 328 15:25 MLAFRA 80
10/05/2013 307 12:40 MUCMLA 63
10/05/2013 353 11:40 DUSMLA 61
12/05/2013 307 12:40 MUCMLA 48
21/05/2013 328 15:25 MLAFRA 54
01/05/2013 353 11:40 DUSMLA 103
03/05/2013 307 12:40 MUCMLA 53
08/05/2013 1309 20:00 MUCCTA 82
11/05/2013 1309 19:55 MUCCTA 53
14/05/2013 328 15:25 MLAFRA 55
18/05/2013 352 08:00 MLADUS 79
26/05/2013 308 07:30 MLAMUC 57
09/05/2013 1308 17:00 CTAMUC 63
13/05/2013 328 15:25 MLAFRA 75
13/05/2013 1309 19:55 MUCCTA 54
15/05/2013 368 08:30 MLAHAM 63
20/05/2013 306 09:25 MLAMUC 69
27/05/2013 1308 17:00 CTAMUC 76
02/05/2013 329 19:15 FRAMLA 53
08/05/2013 377 18:45 TXLMLA 86
09/05/2013 329 19:15 FRAMLA 91
20/05/2013 352 16:00 MLADUS 92
28/05/2013 328 15:25 MLAFRA 54
08/05/2013 352 08:00 MLADUS 63
12/05/2013 328 15:25 MLAFRA 87
16/05/2013 1308 17:00 CTAMUC 66
21/05/2013 306 09:25 MLAMUC 59
03/05/2013 353 11:40 DUSMLA 80
04/05/2013 328 15:25 MLAFRA 78
10/05/2013 377 13:10 TXLMLA 82
15/05/2013 329 19:15 FRAMLA 57
19/05/2013 329 19:15 FRAMLA 86
26/05/2013 377 14:10 TXLMLA 98
09/05/2013 1309 20:00 MUCCTA 75
19/05/2013 368 08:30 MLAHAM 59
27/05/2013 328 15:25 MLAFRA 75
05/05/2013 369 12:25 HAMMLA 72
17/05/2013 376 09:30 MLATXL 72
18/05/2013 353 11:40 DUSMLA 108
26/05/2013 309 10:35 MUCMLA 55
08/05/2013 1308 17:00 CTAMUC 60
20/05/2013 353 19:35 DUSMLA 66
21/05/2013 307 12:40 MUCMLA 108
28/05/2013 329 19:15 FRAMLA 30
15/05/2013 377 18:45 TXLMLA 67



and this is the second part without the percentages where i have to compare from the above data and add the relevent % to it






Fdate Month Year Market Flt DOW
01/05/2013 May 2013 TXLMLA 377 WED
31/05/2013 May 2013 MLADUS 352 FRI
31/05/2013 May 2013 MLADUS 352 FRI
02/05/2013 May 2013 MUCMLA 307 THURS
12/05/2013 May 2013 MLADUS 352 SUN
23/05/2013 May 2013 FRAMLA 329 THURS
05/05/2013 May 2013 MLADUS 352 SUN
23/05/2013 May 2013 MUCMLA 307 THURS
05/05/2013 May 2013 MLAHAM 368 SUN
27/05/2013 May 2013 MLADUS 352 MON
27/05/2013 May 2013 MLADUS 352 MON
09/05/2013 May 2013 DUSMLA 353 THURS
05/05/2013 May 2013 MLAMUC 306 SUN
08/05/2013 May 2013 MLAHAM 368 WED
10/05/2013 May 2013 MLAFRA 328 FRI
17/05/2013 May 2013 MLATXL 376 FRI
02/05/2013 May 2013 MUCCTA 1309 THURS
11/05/2013 May 2013 DUSMLA 353 SAT
26/05/2013 May 2013 DUSMLA 353 SUN
02/05/2013 May 2013 MLAFRA 328 THURS
06/05/2013 May 2013 CTAMUC 1308 MON
09/05/2013 May 2013 MLADUS 352 THURS
12/05/2013 May 2013 HAMMLA 369 SUN
16/05/2013 May 2013 MUCMLA 307 THURS
20/05/2013 May 2013 MLAMUC 306 MON
22/05/2013 May 2013 FRAMLA 329 WED
24/05/2013 May 2013 FRAMLA 329 FRI
02/05/2013 May 2013 MLADUS 352 THURS
09/05/2013 May 2013 MLAFRA 328 THURS
10/05/2013 May 2013 MLADUS 352 FRI
15/05/2013 May 2013 MLATXL 376 WED
19/05/2013 May 2013 MLADUS 352 SUN
15/05/2013 May 2013 TXLMLA 377 WED
03/05/2013 May 2013 MLATXL 376 FRI
04/05/2013 May 2013 DUSMLA 353 SAT
06/05/2013 May 2013 MLAMUC 306 MON
16/05/2013 May 2013 CTAMUC 1308 THURS
18/05/2013 May 2013 MLAMUC 306 SAT
20/05/2013 May 2013 DUSMLA 353 MON
26/05/2013 May 2013 MLADUS 352 SUN
26/05/2013 May 2013 MLAHAM 368 SUN
27/05/2013 May 2013 MUCMLA 307 MON
27/05/2013 May 2013 MUCMLA 307 MON
02/05/2013 May 2013 MLAMUC 306 THURS
10/05/2013 May 2013 MLAMUC 306 FRI
10/05/2013 May 2013 MLATXL 376 FRI
16/05/2013 May 2013 FRAMLA 329 THURS
24/05/2013 May 2013 MLADUS 352 FRI
18/05/2013 May 2013 MLAFRA 328 SAT
22/05/2013 May 2013 MLADUS 352 WED
22/05/2013 May 2013 MLADUS 352 WED
24/05/2013 May 2013 MUCMLA 307 FRI
01/05/2013 May 2013 MLAHAM 368 WED
08/05/2013 May 2013 MLADUS 352 WED
09/05/2013 May 2013 CTAMUC 1308 THURS
10/05/2013 May 2013 FRAMLA 329 FRI
10/05/2013 May 2013 MUCMLA 307 FRI
13/05/2013 May 2013 FRAMLA 329 MON
15/05/2013 May 2013 MLAHAM 368 WED
16/05/2013 May 2013 DUSMLA 353 THURS
22/05/2013 May 2013 MUCCTA 1309 WED
06/05/2013 May 2013 FRAMLA 329 MON
06/05/2013 May 2013 MUCMLA 307 MON
04/05/2013 May 2013 MUCMLA 307 SAT
10/05/2013 May 2013 DUSMLA 353 FRI
22/05/2013 May 2013 TXLMLA 377 WED
24/05/2013 May 2013 TXLMLA 377 FRI
25/05/2013 May 2013 MLADUS 352 SAT
02/05/2013 May 2013 CTAMUC 1308 THURS
05/05/2013 May 2013 MUCMLA 309 SUN
08/05/2013 May 2013 CTAMUC 1308 WED
15/05/2013 May 2013 MLADUS 352 WED
24/05/2013 May 2013 MLAFRA 328 FRI
24/05/2013 May 2013 MLAMUC 306 FRI
29/05/2013 May 2013 MUCMLA 307 WED
30/05/2013 May 2013 MLADUS 352 THURS
29/05/2013 May 2013 MUCMLA 307 WED
30/05/2013 May 2013 MLADUS 352 THURS
09/05/2013 May 2013 MLAMUC 306 THURS
13/05/2013 May 2013 MUCMLA 307 MON
19/05/2013 May 2013 MLAHAM 368 SUN
21/05/2013 May 2013 MLAMUC 306 TUES
25/05/2013 May 2013 MUCMLA 307 SAT
03/05/2013 May 2013 FRAMLA 329 FRI
03/05/2013 May 2013 FRAMLA 329 FRI
07/05/2013 May 2013 MLAMUC 306 TUES
08/05/2013 May 2013 MLAMUC 306 WED
08/05/2013 May 2013 MLATXL 376 WED
16/05/2013 May 2013 MLADUS 352 THURS
22/05/2013 May 2013 MLAHAM 368 WED
29/05/2013 May 2013 MLAHAM 368 WED
29/05/2013 May 2013 MLAHAM 368 WED
06/05/2013 May 2013 DUSMLA 353 MON
07/05/2013 May 2013 FRAMLA 329 TUES
07/05/2013 May 2013 MUCMLA 307 TUES
14/05/2013 May 2013 MLAMUC 306 TUES
15/05/2013 May 2013 FRAMLA 329 WED
18/05/2013 May 2013 CTAMUC 1308 SAT
26/05/2013 May 2013 MLAMUC 308 SUN
22/05/2013 May 2013 DUSMLA 353 WED
23/05/2013 May 2013 DUSMLA 353 THURS
23/05/2013 May 2013 MLAFRA 328 THURS
03/05/2013 May 2013 TXLMLA 377 FRI
11/05/2013 May 2013 FRAMLA 329 SAT
12/05/2013 May 2013 FRAMLA 329 SUN
14/05/2013 May 2013 MLAFRA 328 TUES
15/05/2013 May 2013 MLAFRA 328 WED
26/05/2013 May 2013 MUCMLA 307 SUN
26/05/2013 May 2013 MUCMLA 309 SUN
29/05/2013 May 2013 TXLMLA 377 WED
29/05/2013 May 2013 TXLMLA 377 WED
13/05/2013 May 2013 MUCCTA 1309 MON
21/05/2013 May 2013 MLAFRA 328 TUES
22/05/2013 May 2013 MLAFRA 328 WED
25/05/2013 May 2013 FRAMLA 329 SAT
28/05/2013 May 2013 MLAFRA 328 TUES
29/05/2013 May 2013 HAMMLA 369 WED
29/05/2013 May 2013 MUCCTA 1309 WED
28/05/2013 May 2013 MLAFRA 328 TUES
29/05/2013 May 2013 HAMMLA 369 WED
29/05/2013 May 2013 MUCCTA 1309 WED
01/05/2013 May 2013 MLADUS 352 WED
02/05/2013 May 2013 FRAMLA 329 THURS
03/05/2013 May 2013 MUCMLA 307 FRI
11/05/2013 May 2013 MUCCTA 1309 SAT
15/05/2013 May 2013 MUCMLA 307 WED
17/05/2013 May 2013 MLAMUC 306 FRI
25/05/2013 May 2013 MUCCTA 1309 SAT
04/05/2013 May 2013 MUCCTA 1309 SAT
06/05/2013 May 2013 MUCCTA 1309 MON
07/05/2013 May 2013 MLAFRA 328 TUES
15/05/2013 May 2013 HAMMLA 369 WED
15/05/2013 May 2013 MLAMUC 306 WED
17/05/2013 May 2013 MLAFRA 328 FRI
31/05/2013 May 2013 TXLMLA 377 FRI
02/05/2013 May 2013 DUSMLA 353 THURS
04/05/2013 May 2013 FRAMLA 329 SAT
13/05/2013 May 2013 DUSMLA 353 MON
16/05/2013 May 2013 MLAMUC 306 THURS
22/05/2013 May 2013 HAMMLA 369 WED
27/05/2013 May 2013 DUSMLA 353 MON
27/05/2013 May 2013 DUSMLA 353 MON
14/05/2013 May 2013 MUCMLA 307 TUES
19/05/2013 May 2013 MLAMUC 308 SUN
22/05/2013 May 2013 CTAMUC 1308 WED
23/05/2013 May 2013 CTAMUC 1308 THURS
30/05/2013 May 2013 DUSMLA 353 THURS
30/05/2013 May 2013 DUSMLA 353 THURS
03/05/2013 May 2013 MLADUS 352 FRI
12/05/2013 May 2013 MUCMLA 307 SUN
14/05/2013 May 2013 FRAMLA 329 TUES
30/05/2013 May 2013 MUCMLA 307 THURS
30/05/2013 May 2013 MUCMLA 307 THURS
11/05/2013 May 2013 MUCMLA 307 SAT
17/05/2013 May 2013 TXLMLA 377 FRI
24/05/2013 May 2013 MLATXL 376 FRI
31/05/2013 May 2013 MLATXL 376 FRI
05/05/2013 May 2013 FRAMLA 329 SUN
05/05/2013 May 2013 MUCMLA 307 SUN
15/05/2013 May 2013 MUCCTA 1309 WED
22/05/2013 May 2013 MLAMUC 306 WED
29/05/2013 May 2013 MLADUS 352 WED
29/05/2013 May 2013 MLADUS 352 WED
15/05/2013 May 2013 CTAMUC 1308 WED
29/05/2013 May 2013 MLATXL 376 WED
29/05/2013 May 2013 MLATXL 376 WED
08/05/2013 May 2013 MLAFRA 328 WED
26/05/2013 May 2013 FRAMLA 329 SUN
27/05/2013 May 2013 FRAMLA 329 MON
27/05/2013 May 2013 FRAMLA 329 MON
12/05/2013 May 2013 MUCMLA 309 SUN
31/05/2013 May 2013 DUSMLA 353 FRI
31/05/2013 May 2013 MUCMLA 307 FRI
31/05/2013 May 2013 DUSMLA 353 FRI
23/05/2013 May 2013 MLADUS 352 THURS
16/05/2013 May 2013 MLAFRA 328 THURS
19/05/2013 May 2013 MLAMUC 306 SUN
23/05/2013 May 2013 MLAMUC 306 THURS
27/05/2013 May 2013 MUCCTA 1309 MON
28/05/2013 May 2013 MUCMLA 307 TUES
27/05/2013 May 2013 MUCCTA 1309 MON
28/05/2013 May 2013 MUCMLA 307 TUES
30/05/2013 May 2013 FRAMLA 329 THURS
30/05/2013 May 2013 FRAMLA 329 THURS
28/05/2013 May 2013 FRAMLA 329 TUES
28/05/2013 May 2013 FRAMLA 329 TUES
30/05/2013 May 2013 MUCCTA 1309 THURS
30/05/2013 May 2013 MUCCTA 1309 THURS
31/05/2013 May 2013 FRAMLA 329 FRI
31/05/2013 May 2013 FRAMLA 329 FRI


hope this helps to understand what i mean. once more thanks a lot
dear Trowa thanks a lot for your reply and sorry for getting back so late. i had to leave this piece on the side and tackle it at a later date . where did you run that code exactly? i have highlighted the whole data on the excel sheet and inserted comand row 1 column N but didnt get any results.

thanks a lot

Martin
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Hi Martin,

You first need to implement the code.
Open your file.
Hit Alt+F11. This will start VBE.
On the top menu click on Insert > Module.
Paste the code in the big white field.
You can close VBE now.
Back at Excel hit Alt+F8 and run the code.

I did find a mistake in the code, so implement the following code:
Sub CompareToAddPercentage()
Dim x As Integer, cell As Range
For Each cell In Range("H2:H191")
    x = 1
    Do
        x = x + 1
        If cell & cell.Offset(0, 4) & cell.Offset(0, 3) = _
            Range("A" & x) & Range("B" & x) & Range("D" & x) Then
            cell.Offset(0, 6) = Range("E" & x).Value
        End If
    Loop Until x = 189
Next cell
End Sub

To further assist you, here is a link where you can download the file I used to test the code (with code included):
http://www.ge.tt/4yghNpg/v/0?c

Best regards,
Trowa
dear Trowa, thanks for the lesson on VBA , quite honestly that was new to me , am quite green on excel. where the reply on percentages remained empty does it mean that there is no match?

otherwise i am really amazed with the results for me it is simply awsome !!!

highly appreciate

Martin
Posts
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Hi Mfrendo,

Gald to see it helped out and yes, empty cells equals no match.

If you like to enter some text instead of leaving it empty use the following code:
Sub CompareToAddPercentage()
Dim x As Integer, cell As Range
For Each cell In Range("H2:H191")
    x = 1
    Do
        x = x + 1
        If cell & cell.Offset(0, 4) & cell.Offset(0, 3) = _
            Range("A" & x) & Range("B" & x) & Range("D" & x) Then
            cell.Offset(0, 6) = Range("E" & x).Value
        End If
    Loop Until x = 189
    If cell.Offset(0, 6) = vbNullString Then cell.Offset(0, 6) = "No match"
Next cell
End Sub

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!