Possible but may require some 'compromising':
=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))
in D1 to F10 does find the 2
(which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value
, though this could be added back later if required.
in say G1 and copied down to suit:
=arrayformula(LARGE(value(D$1:F$10),row()))
then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!
):
=unique(G:G)
The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0
would have come first and there are quite a few of those.
Concatenate results with ="Value "&H1
etc if required.
The Arrayformula can keep you from copying the formula down. For your use, you want to use vlookup to find and return the value. You will only be able to return the values for one matched domain, so I will assume this is coming from column H in your images.
The simple version places a formula in each column, starting with the USERNAME, place this in cell B2 and make sure cells B3 to the last cell in the column are empty:
=ARRAYFORMULA( IF(ISBLANK(H2:H),,VLOOKUP(H2:H,Sheet2!A2:C,3,FALSE)))
This breaks down as follows. The IF(ISBLANK(H2:H),,
takes care of empty rows, returning no value if cell H in a given row. Note that this is very different from returning "" such as IF(ISBLANK(H2:H),"",
which in Google Sheets actually returns a value of nothing. The VLOOKUP() looks in Sheet2, columns A through C for a value from column H. If one is found it returns the value from the third column. You could do a similar function in cell C2, making a couple minor changes.
If you want to get REALLY tricky, you can combine both of these and place the formlua in cell B1, making header entries and populating the columns with one formula:
=ARRAYFORMULA(IF(ROW(A1:A) = 1, {"USERNAME", "PASSWORD"}, IF(ISBLANK(H1:H),, {VLOOKUP(H1:H, Sheet2!A2:C, 3), FALSE), VLOOKUP(H1:H, Sheet2!A2:D, 4, FALSE)})))
This first checks to see if the arrayformula is working in row 1. If it is, it returns an array of the headers via {"USERNAME", "PASSWORD"}
The curly brackets define an array. The second set of curly brackets returns an array of the two VLOOKUP formulas.
Make sure there is NOTHING else in any of the cells in columns B or C.
Best Answer
SUBTOTAL
to evaluate hidden rowspaste this in C3 cell and drag down:
then paste this into B1 cell: