so, picking up on Clarity's post, you would add 2 columns -- one to hold the name, and one for the address. So let's presume your combined name + address is in column C.
Column A will hold your names, Column B the addresses
In A, use this =LEFT(C1,FIND("...
So in A1:C3 you have {7,6,3,8,3,6,6,2,4}
1 2 3
A 7 8 6
B 6 3 2
C 3 6 4
you could in another cell use the following array formula (use CTRL+SHIFT+ENTER to enter either of the formulas below - one using sumproduct, other using sumif)...
crap, must have grabbed the wrong cell. ***change all the $A32 to $A23 and change all the MATCH(D$... and VLOOKUP(D$.... to MATCH(B$... and VLOOKUP(B$....
how about this... here is the CORRECT formula for B23
=IF(AND($A23 >= INDEX($F$1:$F$17,MATCH(B$21,$G$1:$G$17,0),1), $A23 <...
1. make sure your 'replacement types' match your headers on the bottom half (e.g. 'Type 1 engine w/ rescue'... as well as all the others).
2. enter the following formula into B23
=IF(AND($A32 >= INDEX($F$1:$F$17,MATCH(D$21,$G$1:$G$17,0),1), $A32 <...
Hi JoshVV,
I'm pretty new here but from the few forum posts I've seen, I don't think there is a way to post a workbook. Can you describe how things are different? Is it just a matter of values being rearranged?
given your example: if the initial data are on Sheet1!A1:D3:
Truck year purchased load duration annual payment
Engine 1 2010 5 30,000
Engine 2 2012 5 10,000
on sheet2, use this in A1:C
YEAR Engine 1 Engine 2
2010
2011
2012
2013
2014
2015
...
Then paste the following array...
Ah, just re-read your question and see you want the concatenated values (comma separated):
You can try this in addition to my previous post. In a fourth column (D), enter this formula in D2 (again presuming your data start on row 2 - headings on 1)
=IF(C2>"",C2...
Hi s8ntandrew.
You can identify the unique values compared across 2 columns with an array formula.
Given your data above, you can add a third column (C) and, presuming your data start on row 2, enter the following formula in C2 (use CTRL+SHIFT+ENTER to enter an array formula)...