Prev: How do I delete the words Page 1 printed across my spreadsheet?
Next: Vlookup and combine all references
From: Anthony on 25 Mar 2010 15:23
I am trying to get a formula to put the values from a list together into one
cell, but the length of the lists are all different lengths and styles. Here
are two examples of the lists I'm dealing with and what I'm trying to do:

A B C
1 AZ2_MO 5-12
2 -7
3 -8
4 AZ3A 01
5 02
6 03
7 04
8 05
9 06
10 07
11 08
12 09
13 10
14 11
15 97
16 -7
17 -8

In cell C1 I would like to have the value: 5-12,-7,-8
In cell C4 I would like to have the value: 01-11,97,-7,-8


Thanks for any help! This discussion board has taught me more than I ever
thought there was to know about excel, and I find I learn something new every
day!

From: tompl on 25 Mar 2010 15:38
In Cell c1 put the formula =A1&B1 then copy it down the column.

tom

"Anthony" wrote:

> I am trying to get a formula to put the values from a list together into one
> cell, but the length of the lists are all different lengths and styles. Here
> are two examples of the lists I'm dealing with and what I'm trying to do:
>
> A B C
> 1 AZ2_MO 5-12
> 2 -7
> 3 -8
> 4 AZ3A 01
> 5 02
> 6 03
> 7 04
> 8 05
> 9 06
> 10 07
> 11 08
> 12 09
> 13 10
> 14 11
> 15 97
> 16 -7
> 17 -8
>
> In cell C1 I would like to have the value: 5-12,-7,-8
> In cell C4 I would like to have the value: 01-11,97,-7,-8
>
>
> Thanks for any help! This discussion board has taught me more than I ever
> thought there was to know about excel, and I find I learn something new every
> day!
>
From: tompl on 25 Mar 2010 15:44
Next time I will read the whole question before I answer it. The formula in
C1 should be =B1 & "," & B2 & "," & B3. You could then copy this formula to
C4.

Tom

"Anthony" wrote:

> I am trying to get a formula to put the values from a list together into one
> cell, but the length of the lists are all different lengths and styles. Here
> are two examples of the lists I'm dealing with and what I'm trying to do:
>
> A B C
> 1 AZ2_MO 5-12
> 2 -7
> 3 -8
> 4 AZ3A 01
> 5 02
> 6 03
> 7 04
> 8 05
> 9 06
> 10 07
> 11 08
> 12 09
> 13 10
> 14 11
> 15 97
> 16 -7
> 17 -8
>
> In cell C1 I would like to have the value: 5-12,-7,-8
> In cell C4 I would like to have the value: 01-11,97,-7,-8
>
>
> Thanks for any help! This discussion board has taught me more than I ever
> thought there was to know about excel, and I find I learn something new every
> day!
>
From: Anthony on 25 Mar 2010 16:08
Tom,
Thanks for the chuckle, and the info. This was the original route I was
going to take and will resort to if a better solution cannot be found by the
end of the day. Unfortunately, it's not quite what I'm looking for. By
copying the formula placed in C1 in C4, the result will show 01,02,03. The
solution I am looking for is 01-11,97,-7,-8. The value in A1 signifies a new
set of values, B1-B3 represent these values. A4 has a brand new identifier,
and cells B4-B17 are the new values. Assume there is another value in A18,
that would mark the new identifier.

Manually adjusting wouldn't be an issue normally, but I have approximately
500 sets of these to go through. I'm thinking some sort of OFFSET function
or pivottable would be applicable, I just can't put my finger on the grouping
or the conditional statement portion. This is the thought process I'm going
in right now:

If A4 has a value, that's the starting point. Range is A4:A?, depending on
when next value >0 is. Say, A18. Take the cell above that value and extend
to column B. Now the range is B4:B17. B4 is the first value in C4. If B4
contains a hyphen, then each value from B4:B17 will be included and seperated
with commas. If B4 does not include a hyphen, the query will search down B
column until the column is no longer in sequential order. i.e., 01, 02, 03,
04, 05, 10, 15... the search will return 01-05, 10, 15.

This may require VBA, but I'm learning that almost nothing is impossible in
Excel and I'm excited to find out! Thanks for the help, Tom!


"tompl" wrote:

> Next time I will read the whole question before I answer it. The formula in
> C1 should be =B1 & "," & B2 & "," & B3. You could then copy this formula to
> C4.
>
> Tom
>
> "Anthony" wrote:
>
> > I am trying to get a formula to put the values from a list together into one
> > cell, but the length of the lists are all different lengths and styles. Here
> > are two examples of the lists I'm dealing with and what I'm trying to do:
> >
> > A B C
> > 1 AZ2_MO 5-12
> > 2 -7
> > 3 -8
> > 4 AZ3A 01
> > 5 02
> > 6 03
> > 7 04
> > 8 05
> > 9 06
> > 10 07
> > 11 08
> > 12 09
> > 13 10
> > 14 11
> > 15 97
> > 16 -7
> > 17 -8
> >
> > In cell C1 I would like to have the value: 5-12,-7,-8
> > In cell C4 I would like to have the value: 01-11,97,-7,-8
> >
> >
> > Thanks for any help! This discussion board has taught me more than I ever
> > thought there was to know about excel, and I find I learn something new every
> > day!
> >
 | 
Pages: 1
Prev: How do I delete the words Page 1 printed across my spreadsheet?
Next: Vlookup and combine all references