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]

 

[Aladin Akyurek]

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

We still have a problem here. A dynamic named range, i.e., SL, confined to a definite range, i.e., riconess22!$A$2:$A$19, is seldom useful.

If the range is definite, just select the range, go to the Name Box, and name it SL. The name SL now will stand for a static range.

If SL must be dynamic, we should have something like:

=riconess22!$A$2:INDEX(riconess22!$A:$A,MATCH(REPT("z",255),riconess22!$A:$A))

 

[Akashwani]

Hi Aladin,

I think I understand what you are saying, I guess instead of using the actual range of my sample data, I should have used a larger range!!
Which would be best to use, $A:$A or something like $A$2:$A$2000 for example? I'm thinking what if there is data in A1 or elsewhere.

Thanks for your contribution on this Aladin, I'm always grateful for any expert input, that will steer me in the right direction.

Ak

 

[riconess22]

Hi Aladin,

I think I understand what you are saying, I guess instead of using the actual range of my sample data, I should have used a larger range!!
Which would be best to use, $A:$A or something like $A$2:$A$2000 for example? I'm thinking what if there is data in A1 or elsewhere.

Thanks for your contribution on this Aladin, I'm always grateful for any expert input, that will steer me in the right direction.

Ak

I still can't get it to work....I think I'm pasting it incorrectly. Is there way that you can load the sheet to this forum so I can just open directly and save it (eliminating the possibility of me transcribing the formulas incorrectly - etc....)

 

[Akashwani]

Hi,

Can you let us know the sheet name(s) and the cell ranges you are using?
Could you also post the formulas you are using?

We may be able to spot something that is a miss.

Ak

 

[riconess22]

I pasted exactly as Akashwani described including renaming the range etc.....look what happens as I add content to columns A and B. When I only enter 1 plus value 0 there is no net. Also, when I get to the 4th variation in the A Column, it seems ignored by the consolidated QTY and Net column, but you can see from the helper columns that it is indeed captured there. Weird.

[TABLE="width: 570"]
<colgroup><col span="2"><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD] [/TD]
[TD]QTY[/TD]
[TD]NET[/TD]
[TD] [/TD]
[TD]NET[/TD]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-1[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 570"]
<colgroup><col span="2"><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD] [/TD]
[TD]QTY[/TD]
[TD]NET[/TD]
[TD] [/TD]
[TD]NET[/TD]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD]aa[/TD]
[TD] [/TD]
[TD]aa[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-2[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 570"]
<colgroup><col span="2"><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD] [/TD]
[TD]QTY[/TD]
[TD]NET[/TD]
[TD] [/TD]
[TD]NET[/TD]
[TD]PLUS[/TD]
[TD]MINUS[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]aa[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD]aa[/TD]
[TD] [/TD]
[TD]aa[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]bb[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD]ccc[/TD]
[TD] [/TD]
[TD]bb[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]ccc[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ccc[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]dddd[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]dddd[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]eeee[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]eeee[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]sdfd[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]sdfd[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]-5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akashwani]

Hi,

At this moment I can't think how to solve the issue you have raised in the first example you have posted, sorry.
How often will you only have one piece of data in PLUS (Column A)?

But regarding your third example, I think that you need to make sure that you have changed all the cell ranges.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2	PLUS	MINUS		QTY	NET		NET	PLUS	MINUS	RESULT
3	aa	aa		1	aa		aa	2	1	1
4	aa	bb		1	ccc		bb	1	1	0
5	bb			1	dddd		ccc	1	0	1
6	ccc			1	eeee		dddd	1	0	1
7	dddd			1	sdfd		eeee	1	0	1
8	eeee						sdfd	1	0	1
9	sdfd
10
11
12
13
14
15
16
Sheet12

As you can see I have changed the cell references compared to the last formulas I posted.
Formula for Named Range SL.....
=Sheet12!$A$3:INDEX(Sheet12!$A$3:$A$16,MATCH(REPT("z",255),Sheet12!$A$3:$A$16))

Sheet name is obviously different.

Ak

 

[riconess22]

Hi,

At this moment I can't think how to solve the issue you have raised in the first example you have posted, sorry.
How often will you only have one piece of data in PLUS (Column A)?

But regarding your third example, I think that you need to make sure that you have changed all the cell ranges.....

Sheet12

	A	B	C	D	E	F	G	H	I	J	K
aa	aa		aa		aa
aa	bb		ccc		bb
bb			dddd		ccc
ccc			eeee		dddd
dddd			sdfd		eeee
eeee						sdfd
sdfd

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/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"]3[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D3 =IF(E3="","",VLOOKUP(E3,$G$3:$J$16,4,0)) E3 {=IF(ROWS($E$3:E3)>COUNTIF($J$3:$J$16,">0"),"",INDEX($G$3:$G$16,SMALL(IF($J$3:$J$16>0,ROW($G$3:$G$16)),ROWS($E$3:E3))-ROW($J$3)+1))} G3 {=IFERROR(INDEX(SL,MATCH(0,COUNTIF(SL,"<"&SL)-SUM(COUNTIF(SL,$G$2:G2)),0)),"")} H3 =IF(G3="","",COUNTIF($A$3:$A$16,G3)) I3 =IF(G3="","",COUNTIF($B$3:$B$16,G3)) J3 =IF(G3="","",H3-I3) <tbody> </tbody>

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

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


As you can see I have changed the cell references compared to the last formulas I posted.
Formula for Named Range SL.....
=Sheet12!$A$3:INDEX(Sheet12!$A$3:$A$16,MATCH(REPT("z",255),Sheet12!$A$3:$A$16))

Sheet name is obviously different.

Ak

You were totally right....thanks = it works!!!

Now that I have it, I think I need it across different sheets and I don't know how (since I'm assuming you can) to reference cell from different sheets. So how do I change so the relationships and functions are identical but the actual columns are on different sheets so Sheet 1: Column A, Sheet 2: Column B (wouldn't actually have to be column "B" on that sheet since it's a new sheet of course), Sheet 3: Column D and E, Sheet 4: G, H, I, J

 

[Akashwani]

Hi,

I wouldn't know where to start if all the data were on different sheets!!
My suggestion would be to have all the data on one sheet and link to the other sheets by using =Sheet1!A3:A16, =Sheet2!B3:B16, =Sheet3!D3:D16 etc.
I'm sorry that I couldn't be of more help.

Ak