I have a sheet with 200 rows. If cell contents of A51-100, and/or A101-150, and/or A151 -200 match contents of cell A1-50, then copy and paste data from the A1-50 row into matching rows?
Sorry I don’t understand your problem completely. If you find the content of A1: A51 in A51: A100, then copy the content of A1: A51 to A51: A100. That does not make sense. It’s already there. Please specify your question.
Juergen
Column a will have names. Data will be input into columns B2-S50. If the names match in the different sets of the A column, I would like to have the data then automatically input into B51-S100,B101-S150 , etc. The names will not be in the same order in columns as in A1-A50. If A3=A57 and A102, then paste data from 3B thru 3S to both rows B57 thru S57 and B102 thru S102
hi @dzigis_33:
‘copy and paste’ is manual, just select the cells you want to copy - ctrl-c - select where to put - ctrl-v,
if you’d like it automatically:
-
if it’s a distinct row-by-row case - B51 gets value of B1 if and only if A51 matches A1 - put ‘=IF($A51=$A1;B1;)’ in B51 (without the quotes), you may copy this formula to other rows (51 to 100) and columns as you like, if you dislike the ‘0’ indicating a no-match use ‘=IF($A51=$A1;B1;"")’ instead, for rows 101 to 150 and 151 to 200 you’ll need small modifications, i’m sure you’ll figure out which,
-
if it’s a search case - B51 should get the value of the Bn cell where n is the number of the row at which you find the value of A51 in the range A1:A50 - e.g. A51=zzz, A1=xxx, A2=yyy, A3=zzz, B51 shall get the value of B3 as A51 matches A3 - try ‘=VLOOKUP($A51;$A$1:$S$50;2;0)’ in B51, can be copied to B52:B100, for col C the ‘2’ must be replaced with a ‘3’, col D a ‘4’ and so on, best prepare one working row and copy that to the others, two caveats: 1) as above - rows above 100 need modifications, 2) if you have duplicates in A1:A50 you’ll get the first one as match, second and subsequent will be omitted,
-
if you need ‘similarity search’ - best match if no exact found - read docu for vlookup, sort the list in rows 1:50, set fourth parameter in vlookup formula to ‘1’ instead of ‘0’,
care for your ‘locale’ setting, my is german and thus ; is the formula delimiter, while , is the decimal separator, english and some other countries need modifications,
there are dozens of other possibilities, from array formulas over macros to pivot tables? … and plenty of our pro’s will suggest you other and / or even better solutions … i hope this helps for the start and is easy to understand … have fun and keep learning …
@Lupp & Cons. - anybody like to produce a list of possibilities?
reg.
b.
P.S. in anticipation of the first follow-up-question: ‘A’ is a ‘relative’ reference to col A, which changes if the formula is copied to another column, ‘$A’ is an ‘absolute’ reference, which will point to col A regardless to which col you copy the formula, similar yields for row numbers - ‘1’ vs. ‘$1’,
Hi dzigis_33,
if I understood you correctly, you always need the “same” formula in cells B51:S200.
=VLOOKUP($A51,$A$1:$S$50,COLUMN(),0)
A51 changed in the later rows to 52, 53 and so on.
I have created an example document. Is that what you wanted?
Copy and paste row data if cell contents match.ods
If this answers your question, please mark it as answered. The little white hook in the grey circle. Turns green on mouseover.
That is exactly what I needed. Now if I have cells with formulas past row 200 that reference data in A51 thru S200, can I make that formula reference the result of the =VLOOKUP ?
just treat them as normal cells, … they are …