Catalogues Q.

[James Perry]

Greetings friend & here's my Q.

Catalogue is abbreviated as Cat.

In a worksheet named Day_1, i enter Cat #01 in cell A2, Cat #16 in cell A3, and so on.

These are for catalogues coming in.

For catalogues issued out i would like to use the minus symbol.

For instance, in cell A18 i enter -Cat #01, in cell J33 i enter -Cat #16, and so on.

Assume each catalogue is represented by a unique ID number as stated above.

At the end of the day i would naturally like a summary of the remaining catalogues based on the catalogue number.

For this i need an excel formula.

Btw...in the first place, how do i "force" excel to accept a minus symbol 4 catalogues issued out...???

Many thanks 4 ur time & effort.

 

[Hui]

James


As soon as you enter a minus sign Excel will interpret that as a number and then try and interpret your entry

Try entering '- Cat #16


Alternatively you would be better off to have column for catalogues coming in/out separately and then drop the -Cat and just use the numbers


Can you post a sample file?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Sajan]

Hello James,

Assuming that the catalog IDs in the list do not repeat, you could try a simple approach like the following to get a summary of the remaining catalog IDs:


=INDEX(CatList, SMALL(IF(NOT(COUNTIF(CatList, MID(CatList,2,50))+COUNTIF(CatList, "-"&CatList)), ROW(CatList)-MIN(ROW(CatList))+1), ROW(A1)))


Enter with Ctrl + Shift + Enter


Copy down till you get an error, indicating that there are no more catalog IDs.


I tried the formula with the following sample data:

[pre]

Cat-#1
Cat-#2
Cat-#3
-Cat-#1
Cat-#5
-Cat-#3
Cat-#4
Cat-#8
Cat-#9
Cat-#10
-Cat-#5
-Cat-#2
-Cat-#4
and got the following result:

Cat-#8
Cat-#9
Cat-#10
#NUM!

[/pre]
On the other hand, if the catalog IDs could repeat (for example, multiple Cat-#01 with or without the corresponding -Cat-#01), the above approach will require some tweaking.


Cheers,

Sajan.

 

[James Perry]

Hello Excel Ninja Hui & Sajan & many thanks.

I will try to upload the sample file if possible.

But here's the scenario:

Just visualise catalogues (abbreviated as cat) coming in & issued out.

U have an excel sheet named Day_1 to enter Cat ID numbers. Some have the negative symbol to represent the cats issued out.

Cat numbers will repeat ( say ten times ) if the same Cat number comes in ten times.

For instance Cat #01 will appear ten times in ten different cells in the worksheet as these may be received at ten different time intervals.

The same rule applies for the cats issued out.

For instance Cat #01 will appear five times in five different cells in the same worksheet with the negative symbol to represent cats issued out at 5 different time intervals.

At the end of the day u have a maze of at numbers, some positive (understood by default) & some with the negative sign for cats issued out.

So at the end of Day_1, i would like an excel formula to give a Count of Catalogues remaining based on Catalogue_Number.

Cheers!

James

 

[Hui]

I still think you would be better off using a single column for the Cat name and a single column for Numbers in/out in = +, Out = -'ve


But I will wait to see your file

 

[SirJB7]

Hi, James Perry!


Why not simply entering integer numbers like 1 in A2, 16 in A3, -1 in A18, 16 in A33, and format column A as follows?

"Cat-#"00;-" Cat-#"00;"";@


Then you'll only have to deal with numbers which would simplify formulas a lot.


Regards!

 

[James Perry]

Very many thanks, Sir, Excel Ninja.

Most definitely, i will try out ur options.

 

[bobhc]

*******************DO NOT POST ANYTHING THIS WEEKEND AS IT WILL DISAPPEAR INTO THE EITHER***************READ THE STICKY************Forum Migration & Freezing on 23AUG2013 [IMPORTANT]************