So i have 3 separate sheets, the first is a general info sheet with names and other information on it, names are in column A. Each name is put into either group A or Group B which is indicated in column C. So beside each name, in column C will be the letter A or B.
The second sheet will contain the names in Group A and the third sheet will contain the names in Group B
what formula can i use to automatically transfer the names from the first sheet into either the second sheet or the third sheet based on which group they are placed in according to the info in column C for the first sheet?
I want all the names in sheets 2 and 3 to start at the top with no spaces or #VALUE! or #NAME? or anything like that in between names. So say i have 3 names in rows 2,3 and 4 the first 2 are in group A and the third is in Group B. The first 2 names will show up in rows 2 and 3 on sheet 2; I want the third name to show up on line 2 of sheet 3 instead of line 4.
i would like to use formulas instead of macros.
thanks in advance.
Transfer names from general sheet to separate other sheets
Using formulae will leave blanks in the "Group" sheets as formulae are cell specific. You can, of course, sort Columns A & C (ascending) in sheet 1 first by using the sort feature in the ribbon or use a macro to sort for you.
Furthermore, if your data set is large, or you expect it to grow large, then formulae will slow proceedings considerably.
So, in your case, I would say that a macro using auto filter, would be your best option. Sorting would not be required.