Help With A Conditional Text Format

[Guest]

I'm trying to enter a ref. in one sheet based on text data entered in another.
Being the Excel genius that I am, I came up with the following formula:
=IF('Purchase Day Book'!B31:B41,"Cinergi","PDB")
The result is "value" when I press enter.
I've tried to find an answer in the search, without success, but I've
enjoyed reading the other questions.
I'm wondering too, what will happen if "Cinergi" appears more than once
between
"B31:B41" in the other sheet.
Any ideas should be most welcome.
Gatsby (Not the Great)

 

[Guest]

I really think you need to decide what to do under the various possibilities.
What should happen if there are 0 instances of "Cinergi", 1 occurence, more
than 1 occurence, etc. The quality of responses will be better if you
provide more details.

This would return "None" for 0 occurences, "PDB" for one instance, and
"Multiple" if it appears more than once.
=CHOOSE(MIN(COUNTIF('Purchase Day
Book'!B31:B41,"Cinergi"),2)+1,"None","PDB","Multiple")

If you want to return PDB if Cinergi appears one or more times:
=IF(COUNTIF('Purchase Day Book'!B31:B41,"Cinergi"),"PDB","")

If you want PDB returned only if "Cinergi" appears exactly once
=IF(COUNTIF('Purchase Day Book'!B31:B41,"Cinergi")=1,"PDB","")

 

[Guest]

Thanks for replying JMB!
When invoices arrive from suppliers they're recorded in the Purchase Day
Book. They're then 'posted' to the supplier's account in the Creditor's
Ledger. I want the supplier account to fill in when the entry is made in the
PDB.
Si, If an invoice is entered for "Cinergi" in the PDB I want that entry on
the first row of his account in the Ledger. If, a few rows down, in the PdB
there is another invoice from "Cinergi" I want that to fill in on the second
row in his account. The invoices in the PDB are entered in date order and
that's the reason they would not be grouped together. But I need the entries
in his account in the Ledger to be grouped together (Like on a Bank Staement.
When I get this sorted I have to the same in adjacent columns in the Ledger
for the values of the invoice.
I was trying to keep original question as brief as possible.
So. Can it be done?
Thanks,
Gatsby (Not the Great)

 

[Guest]

There are ways to pull those values out w/formulae, but those formulae would
have to reside in the suppliers account tab and you won't know beforehand how
many rows of formulae you need. And if you have a lot of data, the formulas
could bog or crash the computer. Accounting software would probably be my
first choice. I've really never worked much w/databases, but I would
probably check it out to see if it is a better solution (particularly if
there is a moderate to large amount of data)

Assuming your data is in A1:B100, and this is entered in F1 (confirmed with
Cntrl+Shift+Enter
=INDEX(B$1:B$100,SMALL(IF(A$1:A$100="Cinergi",ROW(A$1:A$100)-ROW(A$1)+1),ROWS(F$1:F1)))

and copied down until you get an error, but I am skeptical it will work well
as I expect you have a moderate amount of data (calculation load will likely
cripple the machine).

You could also try this:
G1 =MATCH("Cinergi",A:A,0)
H1 =INDEX(B:B,G1)
G2 =MATCH("Cinergi",INDEX(A:A,G1+1):A$65536,0)+G1
H2 =INDEX(B:B,G2)

and copy G2:H2 down until you get an error. Pulling out 27K entries from a
data set of 57K took about 4.5 minutes to calculate - not great but it didn't
crash.

Or, you could use Autofilter (Data/Filter/Autofilter) to filter for Cinergi
and copy/paste the results to the suppliers account - which could probably be
semi-automated.

These suggestions are rather simplistic, I'm sure there are better/faster
methods. Perhaps someone else has designed a project similar to what you are
doing and has better suggestions to offer.

 

[Guest]

Thanks again for replying, JMB.
You've gone to a lot of trouble for me and I appreciate it.
Actually there are never any more than ten supplier invoices in a month.
Which will probably make you ask why I just dont cop[y everything and paste
to the individual accounts. It's just that I'm kinda getting addicted to this
Excel. I started off with a blank sheet. Hadn't a clue what Excel was about.
And now I have 'my accounts' looking really professional on screen. I did all
this through trial and error. I put formulae into the payroll and it means I
have only to make one entry and everything is calculated down to the
employees' take-home pay and the taxes due to the Government. I hasten to add
that most of these formulae were supplied by people, like your good self, in
this group. There is no way I would ever have been able to do the payroll
otherwise. Anyone that has seen it on the screen thinks I have purchased a
commercial package. I'm trying to emulate this now with the accounts and, as
I said above, there aren't that many invoices involved and I'm enjoying
trying to do it. I know people who have bought the accounting software and
spend most of their time on the phone looking for support from the
manfacturers of the software because they don't know what to do ewhen
something doesn't happen the way they want it to. If I ask them about the
software they tell me that they don't have a clue what happens when they use
the package. Me? I like to know exactly what's happening. Two of my friends
bought accounting software and they still had to retain the services of the
book keeper to operate the software programme on the Pc. Now the book keepers
are spending more time filing their nails than filing accounts but they have
to retained because my friend don't know what they are doing when they input
into the software themselves. Sorry for waffling on. Anyway, the auto filter
looks like it's worth a try.
What d'ya reckon? For a small amount of invoices the computer should be ok.
By the way, I have to say that I feel awful when I'm asking questions of the
group because I have nothing to contribute. I can't answer the questions at
all. I really admire you guys, though, for the trouble you all go to, to help
others.
It's really appreciated,
Gatsby (Not the Great)

 

[Guest]

Either the autofilter or you might try the array formula (since there are
relatively few invoices it might calculate w/o any problems). If the data is
in A1:B100, with "Cinergi" in column A and the data you want to pull out in
column B, and this formula was entered into cell F1:

=INDEX(B$1:B$100,SMALL(IF(A$1:A$100="Cinergi",ROW(A$1:A$100)-ROW(A$1)+1),ROWS(F$1:F1)))

confirmed w/Cntrl+Shift+Enter. you can't use entire columns w/array
formulae (except for XL2007, I understand), but you could use a range that is
one row less than a full column (eg A1:A65535).

Thanks again for replying, JMB.
You've gone to a lot of trouble for me and I appreciate it.
Actually there are never any more than ten supplier invoices in a month.
Which will probably make you ask why I just dont cop[y everything and paste
to the individual accounts. It's just that I'm kinda getting addicted to this
Excel. I started off with a blank sheet. Hadn't a clue what Excel was about.
And now I have 'my accounts' looking really professional on screen. I did all
this through trial and error. I put formulae into the payroll and it means I
have only to make one entry and everything is calculated down to the
employees' take-home pay and the taxes due to the Government. I hasten to add
that most of these formulae were supplied by people, like your good self, in
this group. There is no way I would ever have been able to do the payroll
otherwise. Anyone that has seen it on the screen thinks I have purchased a
commercial package. I'm trying to emulate this now with the accounts and, as
I said above, there aren't that many invoices involved and I'm enjoying
trying to do it. I know people who have bought the accounting software and
spend most of their time on the phone looking for support from the
manfacturers of the software because they don't know what to do ewhen
something doesn't happen the way they want it to. If I ask them about the
software they tell me that they don't have a clue what happens when they use
the package. Me? I like to know exactly what's happening. Two of my friends
bought accounting software and they still had to retain the services of the
book keeper to operate the software programme on the Pc. Now the book keepers
are spending more time filing their nails than filing accounts but they have
to retained because my friend don't know what they are doing when they input
into the software themselves. Sorry for waffling on. Anyway, the auto filter
looks like it's worth a try.
What d'ya reckon? For a small amount of invoices the computer should be ok.
By the way, I have to say that I feel awful when I'm asking questions of the
group because I have nothing to contribute. I can't answer the questions at
all. I really admire you guys, though, for the trouble you all go to, to help
others.
It's really appreciated,
Gatsby (Not the Great)

There are ways to pull those values out w/formulae, but those formulae would
have to reside in the suppliers account tab and you won't know beforehand how
many rows of formulae you need. And if you have a lot of data, the formulas
could bog or crash the computer. Accounting software would probably be my
first choice. I've really never worked much w/databases, but I would
probably check it out to see if it is a better solution (particularly if
there is a moderate to large amount of data)

Assuming your data is in A1:B100, and this is entered in F1 (confirmed with
Cntrl+Shift+Enter)
=INDEX(B$1:B$100,SMALL(IF(A$1:A$100="Cinergi",ROW(A$1:A$100)-ROW(A$1)+1),ROWS(F$1:F1)))

and copied down until you get an error, but I am skeptical it will work well
as I expect you have a moderate amount of data (calculation load will likely
cripple the machine).

You could also try this:
G1 =MATCH("Cinergi",A:A,0)
H1 =INDEX(B:B,G1)
G2 =MATCH("Cinergi",INDEX(A:A,G1+1):A$65536,0)+G1
H2 =INDEX(B:B,G2)

and copy G2:H2 down until you get an error. Pulling out 27K entries from a
data set of 57K took about 4.5 minutes to calculate - not great but it didn't
crash.

Or, you could use Autofilter (Data/Filter/Autofilter) to filter for Cinergi
and copy/paste the results to the suppliers account - which could probably be
semi-automated.

These suggestions are rather simplistic, I'm sure there are better/faster
methods. Perhaps someone else has designed a project similar to what you are
doing and has better suggestions to offer.