I have my source of data in Columns N, O, P and it looks like the following:

ColN ColO ColP
123A4567 x/xx/2007 $3.00
123A4567 x/xx/2008 $2.90
123A4567 x/xx/2009 $2.75
123A4567 x/xx/2010 $2.60
123A4567 x/xx/2011 $2.45
123A4567 x/xx/2012 $2.35
456N7890 x/xx/2007 $19.00
456N7890 x/xx/2008 $18.90
456N7890 x/xx/2009 $18.75
456N7890 x/xx/2010 $18.60
456N7890 x/xx/2011 $18.45
456N7890 x/xx/2012 $18.35

I have the individual part numbers in Col A already and would like to match
Column A to Column N and then take the price from Col P and place it in
Columns G thru L on that same line transposing the numbers $3.00 through
$2.35 The end result will look like this:

Col A ColB-F(not used) ColG ColH ColI ColJ ColK ColL
123A4567 $3.00 2.90 2.75 2.60 2.45 2.35

It would be nice to have a function that I can drag down the spreadsheet and
fill in everything. In some cases I may have thousands of rows to fill in
this format. If you need to know more let me know. Thanks alot in advance!

Try this:
A2: holds criteria

G2:
=IF(ISERR(SMALL(IF(ColN=$A2,ROW(INDIRECT("1:"&ROWS (ColP)))),COLUMNS($G:G))),"",INDEX(ColP,SMALL(IF(C olN=$A2,ROW(INDIRECT("1:"&ROWS(ColP)))),COLUMNS($G :G))))

ctrl+shift+enter, not just enter
copy across


"Walter" wrote:

I have my source of data in Columns N, O, P and it looks like the following:

ColN ColO ColP
123A4567 x/xx/2007 $3.00
123A4567 x/xx/2008 $2.90
123A4567 x/xx/2009 $2.75
123A4567 x/xx/2010 $2.60
123A4567 x/xx/2011 $2.45
123A4567 x/xx/2012 $2.35
456N7890 x/xx/2007 $19.00
456N7890 x/xx/2008 $18.90
456N7890 x/xx/2009 $18.75
456N7890 x/xx/2010 $18.60
456N7890 x/xx/2011 $18.45
456N7890 x/xx/2012 $18.35

I have the individual part numbers in Col A already and would like to match
Column A to Column N and then take the price from Col P and place it in
Columns G thru L on that same line transposing the numbers $3.00 through
$2.35 The end result will look like this:

Col A ColB-F(not used) ColG ColH ColI ColJ ColK ColL
123A4567 $3.00 2.90 2.75 2.60 2.45 2.35

It would be nice to have a function that I can drag down the spreadsheet and
fill in everything. In some cases I may have thousands of rows to fill in
this format. If you need to know more let me know. Thanks alot in advance!

I copied the formula into the G2 cell. Highlighted the formula and hit
ctrl+shift+enter which put "{}" these brackets around the whole formula and
then I copied it to all the cells. However, I am only pulling blanks...
What is this formula supposed to do?

"Teethless mama" wrote:

Try this:
A2: holds criteria

G2:
=IF(ISERR(SMALL(IF(ColN=$A2,ROW(INDIRECT("1:"&ROWS (ColP)))),COLUMNS($G:G))),"",INDEX(ColP,SMALL(IF(C olN=$A2,ROW(INDIRECT("1:"&ROWS(ColP)))),COLUMNS($G :G))))

ctrl+shift+enter, not just enter
copy across


"Walter" wrote:

I have my source of data in Columns N, O, P and it looks like the following:

ColN ColO ColP
123A4567 x/xx/2007 $3.00
123A4567 x/xx/2008 $2.90
123A4567 x/xx/2009 $2.75
123A4567 x/xx/2010 $2.60
123A4567 x/xx/2011 $2.45
123A4567 x/xx/2012 $2.35
456N7890 x/xx/2007 $19.00
456N7890 x/xx/2008 $18.90
456N7890 x/xx/2009 $18.75
456N7890 x/xx/2010 $18.60
456N7890 x/xx/2011 $18.45
456N7890 x/xx/2012 $18.35

I have the individual part numbers in Col A already and would like to match
Column A to Column N and then take the price from Col P and place it in
Columns G thru L on that same line transposing the numbers $3.00 through
$2.35 The end result will look like this:

Col A ColB-F(not used) ColG ColH ColI ColJ ColK ColL
123A4567 $3.00 2.90 2.75 2.60 2.45 2.35

It would be nice to have a function that I can drag down the spreadsheet and
fill in everything. In some cases I may have thousands of rows to fill in
this format. If you need to know more let me know. Thanks alot in advance!

ColP and ColN are defined names ranges for a range of columnP and a range of
ColumnN. You can not use a whole column.

"Walter" wrote:

I copied the formula into the G2 cell. Highlighted the formula and hit
ctrl+shift+enter which put "{}" these brackets around the whole formula and
then I copied it to all the cells. However, I am only pulling blanks...
What is this formula supposed to do?

"Teethless mama" wrote:

Try this:
A2: holds criteria

G2:
=IF(ISERR(SMALL(IF(ColN=$A2,ROW(INDIRECT("1:"&ROWS (ColP)))),COLUMNS($G:G))),"",INDEX(ColP,SMALL(IF(C olN=$A2,ROW(INDIRECT("1:"&ROWS(ColP)))),COLUMNS($G :G))))

ctrl+shift+enter, not just enter
copy across


"Walter" wrote:

I have my source of data in Columns N, O, P and it looks like the following:

ColN ColO ColP
123A4567 x/xx/2007 $3.00
123A4567 x/xx/2008 $2.90
123A4567 x/xx/2009 $2.75
123A4567 x/xx/2010 $2.60
123A4567 x/xx/2011 $2.45
123A4567 x/xx/2012 $2.35
456N7890 x/xx/2007 $19.00
456N7890 x/xx/2008 $18.90
456N7890 x/xx/2009 $18.75
456N7890 x/xx/2010 $18.60
456N7890 x/xx/2011 $18.45
456N7890 x/xx/2012 $18.35

I have the individual part numbers in Col A already and would like to match
Column A to Column N and then take the price from Col P and place it in
Columns G thru L on that same line transposing the numbers $3.00 through
$2.35 The end result will look like this:

Col A ColB-F(not used) ColG ColH ColI ColJ ColK ColL
123A4567 $3.00 2.90 2.75 2.60 2.45 2.35

It would be nice to have a function that I can drag down the spreadsheet and
fill in everything. In some cases I may have thousands of rows to fill in
this format. If you need to know more let me know. Thanks alot in advance!