Copy multiple data columns with the same key from another sheet excel -
i want copy multiple columns same key 1 excel sheet another.
example:
sheet 1:
123 | | aa 123 | b | bb 123 | c | cc 234 | d | dd 234 | e | ee 55 | f | ff 123 | g | gg
sheet 2:
one cell put key; in case 123
; , want have every column sheet 1 shown on sheet 2, key 123
.
has on sheet 2:
key:123 automaticly shown: 123 | | aa 123 | b | bb 123 | c | cc 123 | g | gg
i guess 1 of easier things do, pretty new excel.
not want use vba code. want use normal functions put in cell, if
etc.
say have data in sheet1 like:
and want retrieve records value 123 in column a. can use:
- autofilter display 123 rows , use copy/paste
- use vba macro copy/paste
- use formulas retrieve rows
to implement third method need to:
- first required row number(s)
- then row's contents using index()
in sheet2 cell a1 enter array formula:
=small(if(sheet1!$a$1:$a$22=123,row($a$1:$a$22),9e+99),row())
and copy cell downwards.
array formulas must entered ctrl + shift + enter rather enter key.
then in sheet2 cell b1 enter normal formula:
=if($a1>22,"",index(sheet1!a:a,$a1))
finally copy b1 , paste rectangular area b1 through d22
sheet2 displays:
(ignore junk appearing @ bottom of column a)
Comments
Post a Comment