Single record with count associated with it. Want to make them indivdual cells

[tsdpurdue]

I am looking for a way to take the size of fish (by tenth of an inch) and the count and make each length its own row:

What I currently have:

Length----- Count
4.0 --------- 2
4.1 --------- 3
4.2 -------- 1
4.3 --------- 5
4.4 -------- 0


What I want:
4.0
4.0
4.1
4.1
4.1
4.2
4.3
4.3
4.3
4.3
4.3

Is there a formula or a function that I can use to get this?

Thanks for all the help!

Tyler

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[newdoverman]

Assuming your data is in A3:B7 with the Titles in row 2 enter a 0 in C2 (Helper column) and then enter this in c3 and fill down

Please Login or Register  to view this content.

Enter this in E2 and fill down

Please Login or Register  to view this content.

	A	B	C	D	E
1			Helper
2	Length	Count	0		4.0
3	4.0	2	2		4.0
4	4.1	3	5		4.1
5	4.2	1	6		4.1
6	4.3	5	11		4.1
7	4.4	0	11		4.2
8					4.3
9					4.3
10					4.3
11					4.3
12					4.3

[tsdpurdue]

That works!! Thank you!

[newdoverman]

Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[tsdpurdue]

Assuming your data is in A3:B7 with the Titles in row 2 enter a 0 in C2 (Helper column) and then enter this in c3 and fill down

Please Login or Register  to view this content.

Enter this in E2 and fill down

Please Login or Register  to view this content.

	A	B	C	D	E
1			Helper
2	Length	Count	0		4.0
3	4.0	2	2		4.0
4	4.1	3	5		4.1
5	4.2	1	6		4.1
6	4.3	5	11		4.1
7	4.4	0	11		4.2
8					4.3
9					4.3
10					4.3
11					4.3
12					4.3

I have been using the formula above to expand the data how I need it. It works great however I discovered for some reason some of the numbers that are generated come out as long decimals:

Example:
2.3
2.3
2.29999999
2.3
2.4
2.5
2.5999999999


What is the best way to get this corrected? I was trying use a pivot table and the numbers are displayed as the same (Because of rounding) however they will not group together

Any ideas or help is greatly appreciated!!

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D
1	Length	Count		Repeats
2	4.0	2		4.0
3	4.1	3		4.0
4	4.2	1		4.1
5	4.3	5		4.1
6	4.4	0		4.1
7				4.2
8				4.3
9				4.3
10				4.3
11				4.3
12				4.3
13	------	------	------	------

This array formula** entered in D2:

=IF(ROWS(D$2:D2)>SUM(B:B),"",INDEX(A$2:A$6,MATCH(TRUE,COUNTIF(D$1:D1,A$2:A$6) < SUMIF(A$2:A$6,A$2:A$6,B$2:B$6),0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Number, 1 decimal place

Copy down until you get blanks.

[tsdpurdue]

Thanks! It seems to be working!!

[Tony Valko]

You're welcome. Thanks for the feedback!