Matching problem, trying again

[Steve]

I have this worksheet where I have items in the third column that
match up with items from the second column corresponding to the tenth
increment decimal numbers in the first column. In the example below,
the first 3 numbers in the third column match up with numbers in the
second column and the rest of the numbers are "0" up until you get to
the next set of tenth decimal numbers starting with 2 in the first
column. I need the fourth column to first list the matching numbers
from both the second and third columns and then list the rest of the
non-matching numbers from the second column, (in place of the zeros).
Any help would be greatly appreciated.

Thanks,

Steve


1.1 30 30 30
1.2 75 167 167
1.3 110 405 405
1.4 167 0 75
1.5 245 0 110
1.6 405 0 245
1.7 455 0 455
2.1 33 33 33
2.2 88 110 110
2.3 110 0 88
2.4 167 0 167
3.1 54 54 110
3.2 124 124 124
3.3 455 455 455

 

[Guest]

Please give an example row by row of what you want and how you get it. What
you state in text and what's displayed don't seem to be consistent to me.

 

[Steve]

Ok, a row by row explanation might be a bit difficult, since my
results in the last colum are based on several numbers within the
first three columns. First of all to clarify what is in each cell,
here goes:

Current Data in columns A thru C

Cells A1 thru A14, should read 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 2.1,
2.2, 2.3, 2.4, 3.1, 3.2 & 3.3, resp
Cells B1 thru B14, should read 30, 75, 110, 167, 245, 405, 455, 33,
88, 110, 167, 54, 124 & 455, resp
Cells C1 thru C14, should read 30, 167, 405, 0, 0, 0, 0, 33, 110, 0,
0, 54, 124 & 455, resp

Desired Output for Column D:

Cells D1 thru D14, should read 30, 167, 405, 75, 110, 245, 455, 33,
110, 88, 167, 110, 124 & 455, resp

If the results in column A were truncated, you would have the
following values, respective to Cells A1 thru A14:

1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3 & 3

If you then view the truncated results as subsets of information
relative to values in the other two columns (B & C) that share the
same subset values, you would have the first 7 rows of information as
a set, the next 4 rows as a set and the last 3 rows as a set.

Now, within the subset of "1" (the first 7 rows of column A), column C
has its first 3 numbers (30, 167 & 405) that are exact matches with
numbers in column B, found within the subset of "1" cells (first 7
rows of column A). Therefore, I need these matched numbers from
column C to carry over to the adjacent cells in column D. Now, where
I have the remaining 4 numbers in column C (all zero's), still within
the subset of "1" (the first 7 rows of column A), I need their
adjacent cells in column D to list the remaining numbers from column B
that did not match up with any of the numbers from column C within
that subset. Therefore, cells D1 thru D7 should read: 30, 167, 405,
75, 110, 245, 455. Whereas, cells D1 thru D3 are 30, 167, 405, resp,
are exact matches with numbers in column B that fall within the subset
of "1" (the first 7 rows of column A) and cells D4 thru D7 are 75,
110, 245, 455, which do not match any of the numbers in column B that
fall within the subset of "1" (the first 7 rows of column A).

I hope this makes more sense... it's a tough problem and even harder
to try and explain.



Regards,

Steve

 

[Guest]

I'm still not getting it. Maybe someone else can figure it out.

 

[Steve]

This may end up appearing twice, but it looks like the Google server
is having some problems this morning at the same time I originally
tried to post my response.....

Ok, a row by row explanation might be a bit difficult, since my
results in the last colum are based on several numbers within the
first three columns. First of all to clarify what is in each cell,
here goes:

Current Data in columns A thru C

Cells A1 thru A14, should read 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 2.1,
2.2, 2.3, 2.4, 3.1, 3.2 & 3.3, resp
Cells B1 thru B14, should read 30, 75, 110, 167, 245, 405, 455, 33,
88, 110, 167, 54, 124 & 455, resp
Cells C1 thru C14, should read 30, 167, 405, 0, 0, 0, 0, 33, 110, 0,
0, 54, 124 & 455, resp

Desired Output for Column D:

Cells D1 thru D14, should read 30, 167, 405, 75, 110, 245, 455, 33,
110, 88, 167, 110, 124 & 455, resp

If the results in column A were truncated, you would have the
following values, respective to Cells A1 thru A14:

1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3 & 3

If you then view the truncated results as subsets of information
relative to values in the other two columns (B & C) that share the
same subset values, you would have the first 7 rows of information as
a set, the next 4 rows as a set and the last 3 rows as a set.

Now, within the subset of "1" (the first 7 rows of column A), column C
has its first 3 numbers (30, 167 & 405) that are exact matches with
numbers in column B, found within the subset of "1" cells (first 7
rows of column A). Therefore, I need these matched numbers from
column C to carry over to the adjacent cells in column D. Now, where
I have the remaining 4 numbers in column C (all zero's), still within
the subset of "1" (the first 7 rows of column A), I need their
adjacent cells in column D to list the remaining numbers from column B
that did not match up with any of the numbers from column C within
that subset. Therefore, cells D1 thru D7 should read: 30, 167, 405,
75, 110, 245, 455. Whereas, cells D1 thru D3 are 30, 167, 405, resp,
are exact matches with numbers in column B that fall within the subset
of "1" (the first 7 rows of column A) and cells D4 thru D7 are 75,
110, 245, 455, which do not match any of the numbers in column B that
fall within the subset of "1" (the first 7 rows of column A).

I hope this makes more sense... it's a tough problem and even harder
to try and explain.



Regards,

Steve

 

[Steve]

This may end up appearing twice, but it looks like the Google server
is having some problems this morning at the same time I originally
tried to post my response.....

Ok, a row by row explanation might be a bit difficult, since my
results in the last colum are based on several numbers within the
first three columns. First of all to clarify what is in each cell,
here goes:

Current Data in columns A thru C

Cells A1 thru A14, should read 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 2.1,
2.2, 2.3, 2.4, 3.1, 3.2 & 3.3, resp
Cells B1 thru B14, should read 30, 75, 110, 167, 245, 405, 455, 33,
88, 110, 167, 54, 124 & 455, resp
Cells C1 thru C14, should read 30, 167, 405, 0, 0, 0, 0, 33, 110, 0,
0, 54, 124 & 455, resp

Desired Output for Column D:

Cells D1 thru D14, should read 30, 167, 405, 75, 110, 245, 455, 33,
110, 88, 167, 110, 124 & 455, resp

If the results in column A were truncated, you would have the
following values, respective to Cells A1 thru A14:

1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3 & 3

If you then view the truncated results as subsets of information
relative to values in the other two columns (B & C) that share the
same subset values, you would have the first 7 rows of information as
a set, the next 4 rows as a set and the last 3 rows as a set.

Now, within the subset of "1" (the first 7 rows of column A), column C
has its first 3 numbers (30, 167 & 405) that are exact matches with
numbers in column B, found within the subset of "1" cells (first 7
rows of column A). Therefore, I need these matched numbers from
column C to carry over to the adjacent cells in column D. Now, where
I have the remaining 4 numbers in column C (all zero's), still within
the subset of "1" (the first 7 rows of column A), I need their
adjacent cells in column D to list the remaining numbers from column B
that did not match up with any of the numbers from column C within
that subset. Therefore, cells D1 thru D7 should read: 30, 167, 405,
75, 110, 245, 455. Whereas, cells D1 thru D3 are 30, 167, 405, resp,
are exact matches with numbers in column B that fall within the subset
of "1" (the first 7 rows of column A) and cells D4 thru D7 are 75,
110, 245, 455, which do not match any of the numbers in column B that
fall within the subset of "1" (the first 7 rows of column A).

I hope this makes more sense... it's a tough problem and even harder
to try and explain.

Regards,

Steve





- Show quoted text -

Anyone else out there care to chime in on this problem? I'm kind of
at a stand still and don't know where to go from here. Any help would
be greatly appreciated.....Thanks, Steve

 

[kounoike]

I don't understand why the value of D12 is not 54, but is 110. so i'm not
quite sure this is what you look for.
but try this one.

Sub sometest()
Dim a
Dim tmp()
Dim i As Long, j As Long, k As Long, n As Long
Dim begin As Long, last As Long

Columns("d").ClearContents
last = Cells(1, "a").End(xlDown).Row
Do While (begin < last)
ReDim tmp(last)
j = 0
i = begin
Do While (Cells(i + 1, "A") <> "")
If Int(Cells(i + 1, "A")) = Int(Cells(i + 2, "A")) Then
tmp(j) = Cells(i + 1, "B")
i = i + 1
j = j + 1
Else
tmp(j) = Cells(i + 1, "B")
Exit Do
End If
Loop
n = i
ReDim Preserve tmp(n - begin)
For j = begin To n
On Error Resume Next
a = Application.Match(Cells(j + 1, "C"), tmp, 0)
If Not IsError(a) Then
Cells(j + 1, "d") = Cells(j + 1, "C")
tmp(a - 1) = ""
k = k + 1
End If
Next

For i = 0 To n - begin
If tmp(i) <> "" Then
For j = begin To n
If Cells(j + 1, "D") = "" Then
Cells(j + 1, "D") = tmp(i)
Exit For
End If
Next
End If
Next
begin = n + 1
Loop
End Sub

keizi

 

[Steve]

I don't understand why the value of D12 is not 54, but is 110. so i'm not
quite sure this is what you look for.
but try this one.

Sub sometest()
Dim a
Dim tmp()
Dim i As Long, j As Long, k As Long, n As Long
Dim begin As Long, last As Long

Columns("d").ClearContents
last = Cells(1, "a").End(xlDown).Row
Do While (begin < last)
ReDim tmp(last)
j = 0
i = begin
Do While (Cells(i + 1, "A") <> "")
If Int(Cells(i + 1, "A")) = Int(Cells(i + 2, "A")) Then
tmp(j) = Cells(i + 1, "B")
i = i + 1
j = j + 1
Else
tmp(j) = Cells(i + 1, "B")
Exit Do
End If
Loop
n = i
ReDim Preserve tmp(n - begin)
For j = begin To n
On Error Resume Next
a = Application.Match(Cells(j + 1, "C"), tmp, 0)
If Not IsError(a) Then
Cells(j + 1, "d") = Cells(j + 1, "C")
tmp(a - 1) = ""
k = k + 1
End If
Next

For i = 0 To n - begin
If tmp(i) <> "" Then
For j = begin To n
If Cells(j + 1, "D") = "" Then
Cells(j + 1, "D") = tmp(i)
Exit For
End If
Next
End If
Next
begin = n + 1
Loop
End Sub

keizi
















- Show quoted text -

Keizi,

Wow! That worked great and you are correct about cell D12....That was
just me transposing the data and missed it when I went back to check
it. One last question, is there any way this could be done using
formulas in cells? It helps me a lot to understand the logic better
when I can see the formulas and how they act on each cell, as I'm not
that familiar with VBA...

Please advise,

Steve

 

[Steve]

Keizi,

Wow! That worked great and you are correct about cell D12....That was
just me transposing the data and missed it when I went back to check
it. One last question, is there any way this could be done using
formulas in cells? It helps me a lot to understand the logic better
when I can see the formulas and how they act on each cell, as I'm not
that familiar with VBA...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Keizi,

I just tried running the macro on a different set of numbers and it
seemed to get hung up once it got to the second subset of numbers from
column A.

350.1 169 169 169
350.2 243 243 243
350.3 390 390 390
353.1 179 179
354.1 180 180
354.2 255 255
354.3 391 391
357.1 30 30
357.2 75 167
357.3 110 405
357.4 167 0
357.5 245 0
357.6 405 0
357.7 455 0
359.1 250 250
361.1 174 174
361.2 242 242
363.1 393 393
364.1 205 249
364.2 249 0
366.1 206 206
366.2 0
368.1 171 171
369.1 256 256
370.1 177 177
370.2 290
370.3 453 290
370.4 0
370.5 0
370.6 0 453
376.1 424 424
377.1 404 404
378.1 403 403


Please advise,

Steve

 

[Steve]

Keizi,

I just tried running the macro on a different set of numbers and it
seemed to get hung up once it got to the second subset of numbers from
column A.

350.1 169 169 169
350.2 243 243 243
350.3 390 390 390
353.1 179 179
354.1 180 180
354.2 255 255
354.3 391 391
357.1 30 30
357.2 75 167
357.3 110 405
357.4 167 0
357.5 245 0
357.6 405 0
357.7 455 0
359.1 250 250
361.1 174 174
361.2 242 242
363.1 393 393
364.1 205 249
364.2 249 0
366.1 206 206
366.2 0
368.1 171 171
369.1 256 256
370.1 177 177
370.2 290
370.3 453 290
370.4 0
370.5 0
370.6 0 453
376.1 424 424
377.1 404 404
378.1 403 403

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Keizi,

Ok. I just figured out what caused the problem I just described. I
had the first row of my spread sheet as labeled columns. Is there a
way that I can tweak the macro to allow it to start at row 2?

Question - did you have to take courses in VBA to get this good or
were you able to just learn it on your own? I can't find any local
schools here that teach VBA....



Please advise,

Steve

 

[kounoike]

Hi Steve

snipped

Sorry, i'm not so much qualified as to explain the logic using formula.
i mean i can't make it using formula in cells so easily. but my logic was
rather straightforward, i mean not so much efficient.

Ok. I just figured out what caused the problem I just described. I
had the first row of my spread sheet as labeled columns. Is there a
way that I can tweak the macro to allow it to start at row 2?

in my former code, add one line as described below.

Sub sometest()
Dim a
Dim tmp()
Dim i As Long, j As Long, k As Long, n As Long
Dim begin As Long, last As Long

Columns("d").ClearContents
last = Cells(1, "a").End(xlDown).Row

begin = 1 '<<== insert this code here

Do While (begin < last)

Question - did you have to take courses in VBA to get this good or
were you able to just learn it on your own? I can't find any local
schools here that teach VBA....

I've not taken any courses to learn VBA. I've read some books and learned
by trial and error and my best partner was VBA debugger and VBA help.
Also there are a lot of good web sites introduced often in this newsgroup.

keizi