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.


"Gatsby" wrote:

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)

"JMB" wrote:

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","Multip le")

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","")


"Gatsby" wrote:

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)