12vStig
Member
Registered: 23rd Dec 07
User status: Offline
|
Need a formula that I'm not even sure exists, best way I can explain it is some sort of range lookup concatenate
I'll try and keep this as simple as possible
Sheet 1,
Consecutive reference numbers in column a
Names in column b e.g.
1 matthew
2 mark
3 luke
4 john
5 Steven
6 joe
7 lee
8 sam
9 michael
10 Peter
Sheet 2,
Column 1 has the first number in a range
Column 2 the last number in the range
Column 3 I need to bring back all the names for that specific range e.g.
1 4 matthew mark luke john
5 7 Steven joe lee
8 10 sam michael Peter
Not too fussed if these are spaced out or not as I can sort that later
Any help greatly appreciated
|
VegasPhil
Premium Member
Registered: 16th Jan 05
Location: Fareham, Hants Drives: Octavia VRS
User status: Offline
|
I'd have thought that filtering would work better for what you are trying to describe and using names?
Corsa 2.0 16v Vegas - Sold
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
I'm sure you could manipulate a Index Match formula to do this but I think (without actually having a go) the data will need to be transposed with headers
[Edited on 22-06-2015 by Gaz]
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
Infact no, INDEX MATCH won't reference the cell's in-between your numbers.
Whats the end goal with having this data in the way you have described? There may be a better way to change this around
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
You can't concatenate an array without adding another function so one way or another you'll be using VB editor.
The easiest way to do this would be like so...
Hit Alt + F11 to open VB editor and paste the following into a module (Insert > Module) to make a new concatenate function
quote:
Function Combine(Rng As Range) As String
Dim C As Range
For Each C In Rng.Cells
Combine = Combine & C.Value & " "
Next C
End Function
then in column C on Sheet2 paste the following...
quote:
=IF(COUNTBLANK(A1:B1)>0,"Range not specified",combine(INDIRECT("Sheet1!B"&A1&":B"&B1)))
The above assumes you've followed your instructions as stated above.
Sheet2 columns A/B will have start and finish row reference and column B on Sheet1 will have the names.
If you'd like to change it from a space inbetween the name to something else like a comma then edit the " " bit in the function code.
Pay me.
[Edited on 22-06-2015 by Brett]
|
12vStig
Member
Registered: 23rd Dec 07
User status: Offline
|
Will give it a whirl, if it works will you accept Czechs?
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
A thank you would suffice
|
12vStig
Member
Registered: 23rd Dec 07
User status: Offline
|
Thank you!
|
