Lookup Values & Transpose
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! |
Lookup Values & Transpose
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! |
Lookup Values & Transpose
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! |
Lookup Values & Transpose
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! |
All times are GMT +1. The time now is 02:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com