Matching problem, trying again

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

0
sspatriots (78)
4/17/2007 8:36:51 PM
excel 39879 articles. 2 followers. Follow

10 Replies
600 Views

Similar Articles

[PageSpeed] 36

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" wrote:

> 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
> 
> 
0
4/17/2007 11:48:04 PM
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


On Apr 17, 7:48 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

0
sspatriots (78)
4/18/2007 2:37:18 PM
I'm still not getting it.   Maybe someone else can figure it out.

"Steve" wrote:

> 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
> 
> 
> On Apr 17, 7:48 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
0
4/18/2007 2:52:05 PM
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




On Apr 17, 7:48 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -


0
sspatriots (78)
4/18/2007 3:08:09 PM
On Apr 18, 11:08 am, Steve <sspatri...@yahoo.com> wrote:
> 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
>
> On Apr 17, 7:48 pm, Barb Reinhardt
>
>
>
> <BarbReinha...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - 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


0
sspatriots (78)
4/19/2007 1:34:35 PM
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" <sspatriots@yahoo.com> wrote in message 
news:1176908889.342055.46050@n76g2000hsh.googlegroups.com...
> 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
>
>
>
>
> On Apr 17, 7:48 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
>> 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" wrote:
>> > 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- Hide quoted text -
>>
>> - Show quoted text -
>
> 

0
kounoike1 (60)
4/19/2007 1:54:43 PM
On Apr 19, 9:54 am, "kounoike" <kouno...@nowherembh.nifty.com> wrote:
> 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" <sspatri...@yahoo.com> wrote in message
>
> news:1176908889.342055.46050@n76g2000hsh.googlegroups.com...
>
>
>
> > 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
>
> > On Apr 17, 7:48 pm, Barb Reinhardt
> > <BarbReinha...@discussions.microsoft.com> wrote:
> >> 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" wrote:
> >> > 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- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - 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

0
sspatriots (78)
4/19/2007 2:18:16 PM
On Apr 19, 10:18 am, Steve <sspatri...@yahoo.com> wrote:
> On Apr 19, 9:54 am, "kounoike" <kouno...@nowherembh.nifty.com> wrote:
>
>
>
>
>
> > 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" <sspatri...@yahoo.com> wrote in message
>
> >news:1176908889.342055.46050@n76g2000hsh.googlegroups.com...
>
> > > 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
>
> > > On Apr 17, 7:48 pm, Barb Reinhardt
> > > <BarbReinha...@discussions.microsoft.com> wrote:
> > >> 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" wrote:
> > >> > 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- Hide quoted text -
>
> > >> - Show quoted text -- Hide quoted text -
>
> > - 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- 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

0
sspatriots (78)
4/19/2007 2:29:20 PM
On Apr 19, 10:29 am, Steve <sspatri...@yahoo.com> wrote:
> On Apr 19, 10:18 am, Steve <sspatri...@yahoo.com> wrote:
>
>
>
>
>
> > On Apr 19, 9:54 am, "kounoike" <kouno...@nowherembh.nifty.com> wrote:
>
> > > 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" <sspatri...@yahoo.com> wrote in message
>
> > >news:1176908889.342055.46050@n76g2000hsh.googlegroups.com...
>
> > > > 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
>
> > > > On Apr 17, 7:48 pm, Barb Reinhardt
> > > > <BarbReinha...@discussions.microsoft.com> wrote:
> > > >> 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" wrote:
> > > >> > 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- Hide quoted text -
>
> > > >> - Show quoted text -- Hide quoted text -
>
> > > - 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- 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- 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

0
sspatriots (78)
4/19/2007 2:36:26 PM
Hi Steve

"Steve" <sspatriots@yahoo.com> wrote in message 
news:1176993386.004888.167550@b75g2000hsg.googlegroups.com...
> On Apr 19, 10:29 am, Steve <sspatri...@yahoo.com> wrote:
>> On Apr 19, 10:18 am, Steve <sspatri...@yahoo.com> wrote:

snipped

>> >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...

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 

0
kounoike1 (60)
4/20/2007 12:00:10 AM
Reply:

Similar Artilces:

Problem with Generic List Remove method
I am having a strange problem with a generic list. Maybe someone can spot my error. Specifically I am trying to remove an item when the list is a list of class object instances. public class myClass { public string myString { get; set; } public int myInt { get; set; } public myClass() { this.myString = string.Empty; this.myInt = 0; } } public class DoSomeWork { public List<myClass> myList = new List<myClass>(); public DoSomeWork() { this.myList = new List<myClass>(); } public void SomeWork() { myClass myClassInstance = null; ...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Publisher/Access problem
I am using MS Office 2000 Professional on a Windows XP Professional OS. (Access 2000 SP-3, Publisher 2000 SP-3) I created a Publisher file called "reunion.pub". It contains: name, address,and other information. I went to reference it using MS Access and the ".pub" file was automatically converted to a ".mdb" file. Now I can't reference it using MS Publisher. Is there any way to convert the file back to a ".pub" file so that it can be used in Publisher? Thank You, Sam Ceccola After managing to set up OE-QuoteFix on his new PC, Ed reads a mes...

Wildcard Problem....
I work at an Insurance Agency and am having a problem with an Exce formula for our application log. Here is the formula that is returnin a zero value to me: =SUMPRODUCT((B4:B200="Hertvik")*(J4:J200="PL*")*(E4:E200)) Column B contains our different agencies. In this instance I want al policies with the "Hertvik" agency. Column J contains policy type (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers i column E for all of the policies with the Hertvik agency and star withe the policy type "PL". However, the * wildcard is not working. ...

Outlook 2002 problem with Eudora
We have a computer that is running Office XP and using Outlook 2002 for their email. Another computer in our office is a MAC that is using Eudora 6.0.1. If the MAC user sends any attachment in their email to the computer running Outlook 2002, the attachments do not come through. If the MAC user sends the same email to a computer in the same office running Outlook 2000, the attachments come through and they are able to open it. The file type is not one that is blocked by XP, we have sent PDF, EXCEL and Word files and none are received. Any help would be really appreciated! Thanks...

further problems
Is it possible to have the reference list located on another sheet to which it will be used as when i try this it tells me i can't --- Message posted from http://www.ExcelForum.com/ Hi Steve The answer is Yes Take a look at Debra Dalgleish's excellent site to see some examples http://www.contextures.com/xlDataVal05.html -- Regards Roger Govier "stevebicks >" <<stevebicks.11f760@excelforum-nospam.com> wrote in message news:stevebicks.11f760@excelforum-nospam.com... > Is it possible to have the reference list located on another sheet to > which it will ...

Problem Installing Hotfix KB895949
Hi Experts Current State of Exchange Server is Windows 2003 / Exchange 2003 SP1 with latest patch MS06-029 which made the store.exe version 6.5.7233.69. - This is causing issues with Blackberry hence need to install Hotfix KB895949. However, We are having trouble applying hotfix kb895949. Add or remove programs says installed but the KB895949.log does not look promising and the Store.exe is still at version 6.5.7233.69 where I would now expect it to be 6.5.7233.51 after applying the KB895949 hotfix. I have included the log below of the installation attempts - any idea ? 0.047: =====...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

ActiveSync problems
Hi I have my mobile device configured to sync with my exchagne server via WLAN or GPRS or whatever connection i have available to me. CAn receive email etc no problems. But when sending email the recipent cannont see anything in the body of the message. (It is blank) I have setup my device to use another exchange server i have and no problems here. Is there setting or something obvious I am missing ? Any help would be appreciated. Cheers On Sun, 23 Oct 2005 19:59:43 +1000, "Thomo^^" <adthomas@westnet.com.au> wrote: >Hi I have my mobile device configured to sync...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

VLOOKUP problem?
VLOOKUP problem Given Column I (C7) has a range of numbers, typically from 47 to 471 (The width of items to be packed in a box). Given a number of box sizes in a Range named Bwsizes, typically: 1 140 2 165 3 190 .. . .. . 11 390 12 415 13 440 14 465 15 490 Required to enter into Column H (C8) the smallest box into which a item will fit. I thought I should be able to do this with VLOOKUP but I've drawn a blank so far. Please help - there are 2,000 of them and there's the depth and hei...

Visible Problem!
I want to make a control to become Visible if in the third Column of my list box[lstModify] shows an "e" ,when I select a row I want the control to be visible The Control now is set no Visible/No Me.cmdEmail.Visible = IIf(IsOwnerWithEmail(Nz(Me.lstModify.Column(3), 0)) _ And IsEmailOn, True, False) Case Else Me.cmdEmail.Visible = False ------------------------------------- I have already a togggle button to email or not: Private Sub cmdSendMail_Click() If cmdSendMail.value = True Then cmdSendMail.Caption = "Send Email" Else c...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Problem with saving form as html
I am trying to generate a html directory with clickable email addresses out of my HR database. I created a from with the text field: ="<a href=""mailto:" & [UseEmail] & """>" & [UseEmail] & "</a>" and save this form as a html file. When loading the html it shows: <a href="mailto:aman0038@umn.edu">aman0038@umn.edu</a> instead of a clickable mail address. The generated source code is: <a href="mailto:aman0038@umn.edu">aman0038@umn.edu</a> How can I prevent...

font problem
I've got a problem with speech marks not appearing in a document with a = certain font. The font I'm using is Abbey Old Style SF. If I type: "some text", = (using shift+2 on my keyboard) the speech marks appear as small squares = instead. =20 However I can go to Windows Character Map, choose Abbey Old Style font, = select the quotation mark, copy it and paste it into the document and = they appear! I've just tried this, and I have the same problem in Word. Please could someone enlighten me. I have to use this font in this = document, and I'd rather not have to c...

Analysis Pack problem
Hi, I am unable to install the Add-ins for Excel. The analysis pack is giving a problem. Kindly help Krish Please be more specific. Do you get an error msg? If so, during what part of the install and what is the message? Thx, JP On Jan 28, 10:16=A0am, "Krishna Kumar L" <glkrishnaku...@hotmail.com> wrote: > Hi, > > =A0 =A0 I am unable to install the Add-ins for Excel. The analysis pack is= > giving a problem. Kindly help > > Krish Yes, I have installed office 2007 and also installed the Add-ins. But when I run Visual Studio and try some a...

Cut & Paste Problem
I am using an Excel within the MsOff2000 Pro version and had this problem that no one seem to be able to provide a good and effective solution. When I do a cut function from a worksheet abc.xls and paste it to another worksheet def.xls, the paste function works fine but the cut data still remain in abc.xls worksheet. Re-installing MsOff2000 Pro did not help. Anyone can help?? Thank you. rhng Roland, Please repost your question a group similar to microsoft.public.office. This group is for the Microsoft CRM application. Matt Parks MVP - Microsoft CRM --------------------------------...

First try collection class- strange error
I copied a sample code and modified name to try collection class here's the definition (constructor has two args) I get error saying "Error 1 'MoldParts' does not contain a constructor that takes '2' arguments " at the bottom of this post is the line that generates the error (called from a form) public class MoldParts : IEnumerable { private string[] elements; MoldParts(string source, char[] delimiters) { // Parse the string into tokens: elements = source.Split(delimiters); } // IEnumerable Interface Implem...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Office 2007 security problem 03-29-10
We have a series of Word 2003 templates. These templates sit on a network folder with NT security permissions of Read & Execute, List Folder Contents, Read, and Write. Not Modify. A user can open these templates, make a change and save the changes using Word 2003. The same user using an Office 2007 installation receives an error message of "Access denied. Contact your administrator". Does anyone know why? ...

problems receiving email
I am using msn XP prof, Office 2003 student,msn premium. I use bell for my high speed internet. I use freedom (from sypatico as a virus protector, and fire wall) I like to use outlook for my email. My problem started about 1 month ago around 22 May 04. When I receive sender name, my name, subj title, but everything is blank after that. To me it sounds like a firewall problem but I didn't apply that specificaly. ...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

perplexing problem
We seem to be experiencing a problem that we have not been able to get a handle on. It also seems to have started to occur at the time that we installed Microsoft CRM (version 3) with the CRM-Exchange connector when an incoming email contains an attachment of another email (probably am eml type), it shows up as an attachment in the incoming email, but when you try to open it, it opens a blank email; actually it is a new email message form. i have asked the sender of one of these messages to send it to another exchange server that does not have the CRM connector (or Network Associates G...