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:

enter image description here

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:

  1. first required row number(s)
  2. 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:

enter image description here

(ignore junk appearing @ bottom of column a)


Comments

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

java - How to filter a backspace keyboard input -

java - Show Soft Keyboard when EditText Appears -