Don't know where to begin (help!)

[dante311]

Thank you in advance!

I have 2 columns.

Weights in column 1 from 30 kg to 150 kg and then how many apples they get in column 2. The catch is each row has a new weight at 1kg heavier. Such that, Row 1 = 30 kg, Row 2 - 31 kg, but in the adjacent column they both get 1 apple. However, by the time you get to a weight of 112, the person starts getting 2 apples (in adjacent column and so on.. It looks like this


Weight |# of Apples
30 | 1
31 | 1
....
112 | 2
113 | 2

And I have a bunch of these... a set for weight to apples, weight to oranges, weight to bananas...

I'm looking for a way to create a summary table for each fruit...

Summary Table
Apples
Weight | # of Apples
30-111 | 1
112-150 | 2


Summary Table
Oranges
Weight |# of Oranges
30-111 | 1
112-140 | 2
140-150 | 3


and so on... but a code I can set across each table to create the summary tables in a different tab?


THANKS!

 

[alansidman]

Build some tables in Sheet2 that look like below

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Weight​

[/TD]
[TD]

Apples​

[/TD]
[TD][/TD]
[TD]

Weight​

[/TD]
[TD]

Oranges​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

30​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

112​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

112​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

140​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

then in Sheet 1, Type this in B2 and copy down. =VLOOKUP(A2,Sheet2!$A$2:$B$3,2,TRUE)

You will need to do this for each fruit Build a lookup table and then populate your data table as above

 

[dante311]

Hi!

Thanks for replying.

I'm looking for the output to resemble this...





Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Weight​

[/TD]
[TD]

Apples​

[/TD]
[TD][/TD]
[TD]

Weight​

[/TD]
[TD]

Oranges​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

30-111​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

30-111​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

112-150​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

112-139​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

140+​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Build some tables in Sheet2 that look like below

then in Sheet 1, Type this in B2 and copy down. =VLOOKUP(A2,Sheet2!$A$2:$B$3,2,TRUE)

You will need to do this for each fruit Build a lookup table and then populate your data table as above

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

A
B
C
D
E
1​

WeightApples
WeightOranges

2​

30​

1​

30​

1​

3​

112​

2​

112​

2​

4​

140​

3​

5​

</body>

 

[alansidman]

In that case, use the =countifs function to find your total counts. Look at this link for a tutorial.

https://www.techonthenet.com/excel/formulas/countifs.php

 

[Fazza]

If I understand, you're wanting to start with a detailed table & create a summary output table. And there are a bunch of these.

For this to be coded, which I think is a good solution approach, please give better description of the source data. (Cause to be able to start writing code there needs to be very clear & specific understanding of the set up.)

 

[dante311]

If I understand, you're wanting to start with a detailed table & create a summary output table. And there are a bunch of these.

For this to be coded, which I think is a good solution approach, please give better description of the source data. (Cause to be able to start writing code there needs to be very clear & specific understanding of the set up.)

So I have a number of tables of different fruits.

Each table has 2 columns with weights from 30kg to 150kg in increments of 1kg. i.e. 30kg, 31kg...149kg, 150kg. Each Weight has a corresponding number of fruits that a person of a particular weight can eat.

So for 30kg-111kg, they can have 1 apple.
112kg-125kg, they can have 2 apples.
126kg-150kg, they can have 3 apples.

So I have a table/s that look like this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight (kg)[/TD]
[TD]# of Apples to Consume[/TD]
[/TR]
[TR]
[TD]30kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]32kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]33kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]34kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]35kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]36kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]148kg[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]149kg[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]150kg[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

And I need an output that looks like this....

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight (kg)[/TD]
[TD]# of Apples to consume[/TD]
[/TR]
[TR]
[TD]30-111kg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]112-125kg[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]126-150kg[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



Thanks!

 

[mse330]

Hi dante311,

Can you achieve that in many ways such as pivot table, formulas, VBA … etc. The below are couple of formulas that should do what you need

Book1
	A	B	C	D	E	F	G	H
1	Weight (kg)	# of Apples		Weight (kg)	# of Apples		Weight (kg)	# of Apples
2	30	1		30 - 111	1		30 - 111	1
3	31	1		112 - 125	2		112 - 125	2
4				126 - 150	3		126 - 150	3
5	110	1
6	111	1
7	112	2
8	113	2
9	114	2
10
11	123	2
12	124	2
13	125	2
14	126	3
15	127	3
16	128	3
17
18	149	3
19	150	3
Sheet1

Cell Formulas
Range	Formula
G2	=MINIFS($A$2:$A$19,$B$2:$B$19,H2)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H2)
G3	=MINIFS($A$2:$A$19,$B$2:$B$19,H3)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H3)
G4	=MINIFS($A$2:$A$19,$B$2:$B$19,H4)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H4)
D2	{=MIN(IF(E2=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E2=$B$2:$B$19,$A$2:$A$19,""))}
D3	{=MIN(IF(E3=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E3=$B$2:$B$19,$A$2:$A$19,""))}
D4	{=MIN(IF(E4=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E4=$B$2:$B$19,$A$2:$A$19,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dante311]

Thanks -

I'll look through the codes and see how I can apply this to the 15 tables I have for 15 different food items

Hi dante311,

Can you achieve that in many ways such as pivot table, formulas, VBA … etc. The below are couple of formulas that should do what you need

	A	B	C	D	E	F	G	H
30 - 111	30 - 111
112 - 125	112 - 125
126 - 150	126 - 150

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;">Weight (kg)[/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;"># of Apples[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;">Weight (kg)[/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;"># of Apples[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;">Weight (kg)[/TD]
[TD="bgcolor: <a href=, align: center"]#FFFF00 ;;"># of Apples[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]112[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]114[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]126[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]128[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]149[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=MINIFS($A$2:$A$19,$B$2:$B$19,H2)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=MINIFS($A$2:$A$19,$B$2:$B$19,H3)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=MINIFS($A$2:$A$19,$B$2:$B$19,H4)&" - "&MAXIFS($A$2:$A$19,$B$2:$B$19,H4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=MIN(IF(E2=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E2=$B$2:$B$19,$A$2:$A$19,""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=MIN(IF(E3=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E3=$B$2:$B$19,$A$2:$A$19,""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=MIN(IF(E4=$B$2:$B$19,$A$2:$A$19,""))&" - "&MAX(IF(E4=$B$2:$B$19,$A$2:$A$19,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

If you prefer a VBA code, post back how your file structure & we'll help you with it.

What I mean by the file structure is are all the 15 tables in the same sheet? The tables consist of only two columns or there are other data ? At which row does your data start ? How do you distinguish between Apple table & Orange table for example … etc.