Index with Alphabetical Sorting and Quantity Consolidation

[riconess22]

I'm looking to implement a basic inventory system with 4 columns. Column one will be a ID's of product scanned in, Column 2 is ID's of product scanned out, Column 3 and 4 show the net result of those two columns. Column 3 is the qty and Column 4 is the ID. Column 4 is unique because it's alphabetized and eliminates duplicates but they are accounted by column 3.

I started playing with Index and sorting, but at the end of the day - I'm sure someone can rip this out in 1/10th of the time and 10 times better. Please help.

[TABLE="width: 275"]
<tbody>[TR]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD]|[/TD]
[TD]QTY[/TD]
[TD]NET[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]BB[/TD]
[TD]|[/TD]
[TD]2[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CC[/TD]
[TD]|[/TD]
[TD]1[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]CC[/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]AA[/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]AA[/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CC[/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisR]

Hi,

Can you post up a little more info? It is quite unclear what you are trying to do?

If you can post up a bit more info hopefully we can assist.

Thanks,
Chris

 

[Akashwani]

Hi,

Is this what you are trying to do?

Excel Workbook
	A	B	C	D	E	F
1	Data1	Data2		Results
2	PLUS	MINUS		QTY	NET
3	BB	BB		2	BB
4	CC	CC		1	CC
5	AA	CC
6	AA	AA
7	BB	AA
8	BB	CC
9	CC
10	CC
11	CC
12
riconess22

Count the number of Items in Data1 and subtract the number of Items in Data2 that match Items in Data1
Then create a list of the Unique Data items that are left and do a count.

Example.....
AA x 2 in Data1 and x 2 in Data2, so 2 minus 2 equals 0, no need to add AA to the NET results.
BB x 3 in Data1 and x 1 in Data2, so 3 minus 1 equals 2, add BB to the NET results along with the QTY (sum/count) result.
CC x 4 in Data1 and x 3 in Data2, so 4 minus 3 equals 1, add CC to the NET results along with the QTY (sum/count) result.

????

Ak

 

[Akashwani]

Hi,

If you are prepared to use a "Helper" table, which can be hidden, then try this....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	PLUS	MINUS	*	QTY	NET	*	NET	Plus	Minus	Result	*
2	BB	BB	*	2	BB	*	AA	2	2	0	*
3	CC	CC	*	1	CC	*	BB	3	1	2	*
4	AA	CC	*	*	*	*	CC	4	3	1	*
5	AA	AA	*	*	*	*	*	*	*	*	*
6	BB	AA	*	*	*	*	*	*	*	*	*
7	BB	CC	*	*	*	*	*	*	*	*	*
8	CC	DD	*	*	*	*	*	*	*	*	*
9	CC	*	*	*	*	*	*	*	*	*	*
10	CC	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*
riconess22

The formula in E2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
The formula in G2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
SL is a Named Range using this formula…..
=$A$3:INDEX($A$3:$A$11,MATCH("zzz",$A$3:$A$11))

See this video....
Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range - YouTube

The other formulas just need entering normally and copied down.

I hope that helps.

Good luck.

Ak

 

[Aladin Akyurek]

Hi,

If you are prepared to use a "Helper" table, which can be hidden, then try this....

riconess22

*	A	B	C	D	E	F	G	H	I	J	K
*	*	*
BB	BB	*	BB	*	AA	*
CC	CC	*	CC	*	BB	*
AA	CC	*	*	*	*	CC	*
AA	AA	*	*	*	*	*	*	*	*	*
BB	AA	*	*	*	*	*	*	*	*	*
BB	CC	*	*	*	*	*	*	*	*	*
CC	DD	*	*	*	*	*	*	*	*	*
CC	*	*	*	*	*	*	*	*	*	*
CC	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 21px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]PLUS[/TD]
[TD="align: center"]MINUS[/TD]

[TD="align: center"]QTY[/TD]
[TD="align: center"]NET[/TD]

[TD="align: center"]NET[/TD]
[TD="align: center"]Plus[/TD]
[TD="align: center"]Minus[/TD]
[TD="align: center"]Result[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

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

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

[TD="bgcolor: #CACACA, align: center"]3[/TD]

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

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

[TD="bgcolor: #CACACA, align: center"]4[/TD]

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

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(E2="","",VLOOKUP(E2,$G$2:$J$4,4,0)) E2 {=IF(ROWS($E$2:E2)>COUNTIF($J$2:$J$4,">0"),"",INDEX($G$2:$G$4,SMALL(IF($J$2:$J$4>0,ROW($G$2:$G$4)),ROWS($E$2:E2))-ROW($J$2)+1))} G2 {=IFERROR(INDEX(SL,MATCH(0,COUNTIF(SL,"<"&SL)-SUM(COUNTIF(SL,$G$1:G1)),0)),"")} H2 =COUNTIF($A$2:$A$10,G2) I2 =COUNTIF($B$2:$B$10,G2) J2 =H2-I2 <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


The formula in E2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
The formula in G2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
SL is a Named Range using this formula…..
=$A$3:INDEX($A$3:$A$11,MATCH("zzz",$A$3:$A$11))

See this video....
Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range - YouTube

The other formulas just need entering normally and copied down.

I hope that helps.

Good luck.

Ak

Ak,

Change A5 to ZZZZ. What do you observe?

And why define SL if you don't use in the formulas at all?

Regards,

Aladin

 

[Akashwani]

Hi Aladin,

I guess I should have used Rept("z",255) like Mike did in the video.

I don't understand this point you raised...
"And why define SL if you don't use in the formulas at all?"
Is it not being used in this....
=IFERROR(INDEX(SL,MATCH(0,COUNTIF(SL,"<"&SL)-SUM(COUNTIF(SL,$G$1:G1)),0)),"")
To put the letters in alphabetical order?

Ak

 

[Aladin Akyurek]

Hi Aladin,

I guess I should have used Rept("z",255) like Mike did in the video.

Yes, that's the point.

I don't understand this point you raised...
"And why define SL if you don't use in the formulas at all?"
Is it not being used in this....
=IFERROR(INDEX(SL,MATCH(0,COUNTIF(SL,"<"&SL)-SUM(COUNTIF(SL,$G$1:G1)),0)),"")
To put the letters in alphabetical order?

Ak

My bad. I see I missed the full usage.

 

[riconess22]

Wow. Awesome, but what's the "Rept("z",255)" mention above? Need I change the suggested formula? I'm confused?

 

[Aladin Akyurek]

Wow. Awesome, but what's the "Rept("z",255)" mention above? Need I change the suggested formula? I'm confused?

Change:

=$A$3:INDEX($A$3:$A$11,MATCH("zzz",$A$3:$A$11))

as:

=$A$3:INDEX($A$3:$A$11,MATCH(REPT"z",255),$A$3:$A$11))

 

[Akashwani]

Hi,

Either right click any cell on your worksheet, click Name a Range, name it SL, and put this formula into the Refers to box...
=riconess22!$A$2:INDEX(riconess22!$A$2:$A$19,MATCH(REPT("z",255),riconess22!$A$2:$A$19))

Or, click the formulas tab, click Name Manager, click New, name it SL, and put this formula into the Refers to box...
=riconess22!$A$2:INDEX(riconess22!$A$2:$A$19,MATCH(REPT("z",255),riconess22!$A$2:$A$19))

You will need to change the sheet name riconess22 and the cell references to suit yours.

Good luck.

Ak