Prev: Already exists on the destination workbook
Next: Synchronous scrolling in all sheets of a wokbook.
From: Glenn on 23 Jan 2009 13:04
heather wrote:
> Well, it doesn't really have anything to do with it. The first question
> didn't work so I am going about it from a different angle....Here is what i
> would like to happen....
> Col A Col B Col C col D
> 699875 0102802NTZG 699875 0102802NTZG
> 345666 0102802NUCU 699875 0107802OBEX
> 548986 0104802NWPZ 699875 0107802OBFA
> 699875 0107802OBEX 548986 0104802NWPZ
> 699875 0107802OBFA etc...
> 462083 0107802OBZY
> 472550 0107802OCDY
>
>
> Column C and D I would like to get some formula to auto enter the info.
> Number from largest to smallest which i used the Larger() ...and Column D to
> pull the corresponding number/letter seq from column B.
>
>

One possible way...

C2 = LARGE($A$2:$A$1000,ROW()-1)

D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,2)&":B1000"),
MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0)))

Adjust the "1000"'s to include all of your data, then copy both down as far as
needed.
From: heather on 23 Jan 2009 13:25


"Glenn" wrote:

> heather wrote:
> > Well, it doesn't really have anything to do with it. The first question
> > didn't work so I am going about it from a different angle....Here is what i
> > would like to happen....
> > Col A Col B Col C col D
> > 699875 0102802NTZG 699875 0102802NTZG
> > 345666 0102802NUCU 699875 0107802OBEX
> > 548986 0104802NWPZ 699875 0107802OBFA
> > 699875 0107802OBEX 548986 0104802NWPZ
> > 699875 0107802OBFA etc...
> > 462083 0107802OBZY
> > 472550 0107802OCDY
> >
> >
> > Column C and D I would like to get some formula to auto enter the info.
> > Number from largest to smallest which i used the Larger() ...and Column D to
> > pull the corresponding number/letter seq from column B.
> >
> >
>
> One possible way...
>
> C2 = LARGE($A$2:$A$1000,ROW()-1)
>
> D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,2)&":B1000"),
> MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
> INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0)))
>
> Adjust the "1000"'s to include all of your data, then copy both down as far as
> needed.
>
Ok, I tried that formula but for the numbers in column A that duplicate all
it returned to me is #num!
From: Glenn on 23 Jan 2009 16:23
heather wrote:
>
> "Glenn" wrote:
>
>> heather wrote:
>>> Well, it doesn't really have anything to do with it. The first question
>>> didn't work so I am going about it from a different angle....Here is what i
>>> would like to happen....
>>> Col A Col B Col C col D
>>> 699875 0102802NTZG 699875 0102802NTZG
>>> 345666 0102802NUCU 699875 0107802OBEX
>>> 548986 0104802NWPZ 699875 0107802OBFA
>>> 699875 0107802OBEX 548986 0104802NWPZ
>>> 699875 0107802OBFA etc...
>>> 462083 0107802OBZY
>>> 472550 0107802OCDY
>>>
>>>
>>> Column C and D I would like to get some formula to auto enter the info.
>>> Number from largest to smallest which i used the Larger() ...and Column D to
>>> pull the corresponding number/letter seq from column B.
>>>
>>>
>> One possible way...
>>
>> C2 = LARGE($A$2:$A$1000,ROW()-1)
>>
>> D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,2)&":B1000"),
>> MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
>> INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0)))
>>
>> Adjust the "1000"'s to include all of your data, then copy both down as far as
>> needed.
>>
> Ok, I tried that formula but for the numbers in column A that duplicate all
> it returned to me is #num!


With the data above it worked fine. Can you put a copy of your worksheet on
www.savefile.com for someone to look at?
From: heather on 23 Jan 2009 16:44
i put it on there....this is the link it gave
me....http://savefile.com/files/1978951
Thanks for the help

"Glenn" wrote:

> heather wrote:
> >
> > "Glenn" wrote:
> >
> >> heather wrote:
> >>> Well, it doesn't really have anything to do with it. The first question
> >>> didn't work so I am going about it from a different angle....Here is what i
> >>> would like to happen....
> >>> Col A Col B Col C col D
> >>> 699875 0102802NTZG 699875 0102802NTZG
> >>> 345666 0102802NUCU 699875 0107802OBEX
> >>> 548986 0104802NWPZ 699875 0107802OBFA
> >>> 699875 0107802OBEX 548986 0104802NWPZ
> >>> 699875 0107802OBFA etc...
> >>> 462083 0107802OBZY
> >>> 472550 0107802OCDY
> >>>
> >>>
> >>> Column C and D I would like to get some formula to auto enter the info.
> >>> Number from largest to smallest which i used the Larger() ...and Column D to
> >>> pull the corresponding number/letter seq from column B.
> >>>
> >>>
> >> One possible way...
> >>
> >> C2 = LARGE($A$2:$A$1000,ROW()-1)
> >>
> >> D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,2)&":B1000"),
> >> MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
> >> INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0)))
> >>
> >> Adjust the "1000"'s to include all of your data, then copy both down as far as
> >> needed.
> >>
> > Ok, I tried that formula but for the numbers in column A that duplicate all
> > it returned to me is #num!
>
>
> With the data above it worked fine. Can you put a copy of your worksheet on
> www.savefile.com for someone to look at?
>
From: Glenn on 23 Jan 2009 16:54
heather wrote:
> i put it on there....this is the link it gave
> me....http://savefile.com/files/1978951
> Thanks for the help
>
> "Glenn" wrote:
>
>> heather wrote:
>>> "Glenn" wrote:
>>>
>>>> heather wrote:
>>>>> Well, it doesn't really have anything to do with it. The first question
>>>>> didn't work so I am going about it from a different angle....Here is what i
>>>>> would like to happen....
>>>>> Col A Col B Col C col D
>>>>> 699875 0102802NTZG 699875 0102802NTZG
>>>>> 345666 0102802NUCU 699875 0107802OBEX
>>>>> 548986 0104802NWPZ 699875 0107802OBFA
>>>>> 699875 0107802OBEX 548986 0104802NWPZ
>>>>> 699875 0107802OBFA etc...
>>>>> 462083 0107802OBZY
>>>>> 472550 0107802OCDY
>>>>>
>>>>>
>>>>> Column C and D I would like to get some formula to auto enter the info.
>>>>> Number from largest to smallest which i used the Larger() ...and Column D to
>>>>> pull the corresponding number/letter seq from column B.
>>>>>
>>>>>
>>>> One possible way...
>>>>
>>>> C2 = LARGE($A$2:$A$1000,ROW()-1)
>>>>
>>>> D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,2)&":B1000"),
>>>> MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
>>>> INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0)))
>>>>
>>>> Adjust the "1000"'s to include all of your data, then copy both down as far as
>>>> needed.
>>>>
>>> Ok, I tried that formula but for the numbers in column A that duplicate all
>>> it returned to me is #num!
>>
>> With the data above it worked fine. Can you put a copy of your worksheet on
>> www.savefile.com for someone to look at?
>>

You said columns C and D, but you really wanted columns H and I.

H2 = LARGE($A$2:$A$1000,ROW()-1)

I2 = IF(H2=H1,INDEX(INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1000,0)+2,2)&":B1000"),
MATCH(H2,INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1000,0)+2,1)&":A1000"),0)),
INDEX($B$2:$B$1000,MATCH(H2,$A$2:$A$1000,0)))
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Already exists on the destination workbook
Next: Synchronous scrolling in all sheets of a wokbook.