Previous Topic Next Topic

Compare columns match and insert data from another column

Closed
Mfrendo Posts 1 Registration date Sunday April 21, 2013 Status Member Last seen April 21, 2013 - Apr 21, 2013 at 05:37 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 27, 2013 at 10:03 AM
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
Related:

12 responses

Answer 1 / 12
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 25, 2013 at 10:51 AM
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
Answer 2 / 12
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 23, 2013 at 02:02 AM
@Mfrendo

Is it possible to upload a sample file with more details

Thanks

Patnaik
0
mfrendo
Apr 23, 2013 at 04:54 AM
tried to import file but so far didnt manage how to upload file here
0
Answer 3 / 12
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 23, 2013 at 08:44 AM
HI

Please try the below mention links
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 23, 2013 at 11:00 AM
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
0
Patnaik
May 22, 2013 at 12:34 AM
@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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 27, 2013 at 10:03 AM
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
0
Answer 4 / 12
mfrendo
Apr 23, 2013 at 10:58 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 12
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 23, 2013 at 11:14 AM
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
0
Answer 6 / 12
mfrendo
Apr 23, 2013 at 11:55 AM
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
0
Answer 7 / 12
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 23, 2013 at 12:16 PM
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
0
Answer 8 / 12
mfrendo
Apr 23, 2013 at 12:40 PM
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
0
Answer 9 / 12
mfrendo
May 16, 2013 at 05:06 AM
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
0
Answer 10 / 12
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 16, 2013 at 10:48 AM
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
0
Answer 11 / 12
mfrendo
May 16, 2013 at 12:57 PM
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
0
Answer 12 / 12
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 21, 2013 at 10:10 AM
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
0

Similar discussions

Comparing columns and returning value
ll -
kittu -

35 responses
Auto generate serial number in excel based on Column
Raj_1562 -
TrowaD -

4 responses
Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses