Auto Splitting of Data to 2 Worksheets

Bellablu · Oct 31, 2013

Hi,

I am creating a workbook for other users to manage money receipting. Problem is receipted data it is processed by two different areas & the data needs to be split & sent respectivly. I am trying to make it simple for the areas receiving the monies as this is not their primary job I am dealing with numerous staff with different skill sets & knowledge.

I have tried to attach my workbook & images but I am not having any luck.

Basically I want the receivers to complete sheet one only of the Cash Book.

I am now trying to work a macro / formula / whatever to auto popuate the further two sheets isolating the Cash/Cheque & EFTPOS payments, which in turn can be just printed & sent off to their respective areas.

Can anyone offer assistance or a solution.

Much appreciated.

venkat1926 · Nov 1, 2013

just copy data(only extract) on the message area.

Bellablu · Nov 2, 2013

Here is the first sheet, where all the data is entered, but depending on the payment method the data needs to be split on to separate sheets & sent to difgferent departments for processing. So I am trying to automate the splitting of data, all EFTPOS transaction to one sheet & all cash/chq transactions to another sheet. The two sheets are exactly the same as the original sheet, I just need them to self populate depending on their payment method. If I have to put a formula in every cell that is fine as I will protect this sheets so no one can play with them.

Payment Method	Date	Receipt #	Name	Amount Received (GST Inc)	Check	Permit	Permit	Licence

EFTPOS	30/10/13	230	Fred Smith	20.00	20.00
Cheque	30/10/13	231	John Doe	40.00		40.00
EFTPOS	30/10/13	232	Luke Harris	60.00			60.00
Cash	31/10/13	233	Darren Loser	20.00				20.00
Cheque	1/11/13	234	Brad Walks	44.00
EFTPOS	1/11/13	235	Erin Win	66.00
Cash	2/11/13	236	Jane Doe	80.00
Cheque	2/11/13	237	Hanry Whinst	20.00

bschwartz · Nov 2, 2013

Try this:

Excel 2010

A

B

C

D

E

F

G

H

I

1

Payment Method

Date

Receipt #

Name

Amount Received (GST Inc)

Check

Permit

Licence

2

EFTPOS

30/10/13

230

Fred Smith

20

3

Cheque

30/10/13

231

John Doe

40

4

EFTPOS

30/10/13

232

Luke Harris

60

5

Cash

31/10/13

233

Darren Loser

20

6

Cheque

1/11/2013

234

Brad Walks

44

7

EFTPOS

1/11/2013

235

Erin Win

66

8

Cash

2/11/2013

236

Jane Doe

80

9

Cheque

2/11/2013

237

Hanry Whinst

20

Transaction Data

Excel 2010

A

B

C

D

E

F

G

H

I

1

Transaction type:

EFTPOS

2

3

Payment Method

Date

Receipt #

Name

Amount Received (GST Inc)

Check

Permit

Licence

4

EFTPOS

30/10/13

230

Fred Smith

20

5

EFTPOS

30/10/13

232

Luke Harris

60

6

EFTPOS

41285

235

Erin Win

66

EFTPOS

Cell Formulas
Range	Formula
A4	=IF(ROWS(A$3:A3)<=COUNTIF('Transaction Data'!$A$2:$A$999,EFTPOS!$B$1),INDEX('Transaction Data'!A$2:A$999,AGGREGATE(15,6,ROW('Transaction Data'!$A$2:$A$999)-ROW('Transaction Data'!$A$2)+1/('Transaction Data'!$A$2:$A$999=$B$1),ROWS(A$3:A3))),"")

Fill formula down and across.

Also to hide zeroes on EFTPOS tab, highlight all of the cells, right click, and select format cells. Go to Number, and Custom, and in the type box use: 0;-0;;@

Lastly, for other worksheets, copy the same format and just change the value in cell B1.

bschwartz · Nov 2, 2013

Slight correction to previously posted formula:

=IF(ROWS(A$3:A3)<=COUNTIF('Transaction Data'!$A$2:$A$999,$B$1),INDEX('Transaction Data'!A$2:A$999,AGGREGATE(15,6,ROW('Transaction Data'!$A$2:$A$999)-ROW('Transaction Data'!$A$2)+1/('Transaction Data'!$A$2:$A$999=$B$1),ROWS(A$4:A4))),"")

Bellablu · Nov 3, 2013

Hi Ben,

Thank you so much for your responce & assistance, sadly its not working for me. I noticed your using Excel 2010, does it matter that I am in Excel 2007? I ended up copying your above sheet examples into a new workbook & tried both formulas just to get a better understanding of the formula & apply it in my workbook, but kept getting the error "#NAME?".

Is thee a way on the forum that I can attch my workbook for you to look at?

Cheers

bschwartz · Nov 3, 2013

Yes, it does matter. I am using a function that is new to 2010. I can replace it with an older function tomorrow.

Bellablu · Nov 3, 2013

Fabulous, thank you so much for your time Ben , it is very much appreciated.

bschwartz · Nov 4, 2013

Try this:

Excel 2010

A

B

C

D

E

F

G

H

I

1

Transaction type:

EFTPOS

2

3

Payment Method

Date

Receipt #

Name

Amount Received (GST Inc)

Check

Permit

Licence

4

EFTPOS

30/10/13

230

Fred Smith

20

5

EFTPOS

30/10/13

232

Luke Harris

60

6

EFTPOS

1/11/2013

235

Erin Win

66

EFTPOS

Cell Formulas
Range	Formula
A4	{=IF(ROWS(A$3:A3)<=COUNTIF('Transaction Data'!$A$2:$A$999,$B$1),INDEX('Transaction Data'!A$2:A$999,SMALL(IF('Transaction Data'!$A$2:$A$999=$B$1,ROW('Transaction Data'!$A$2:$A$999)-ROW('Transaction Data'!$A$2)+1),ROWS(A$4:A4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Bellablu · Nov 5, 2013

Hi Ben,

What do you mean "Entered with Ctrl+Shift+Enter" I have not used that before. Do I type in the in the formula or cut & paste. I am confused now.

Auto Splitting of Data to 2 Worksheets

Bellablu

New Member

Excel Facts

venkat1926

Well-known Member

Bellablu

New Member

bschwartz

Well-known Member

bschwartz

Well-known Member

Bellablu

New Member

bschwartz

Well-known Member

Bellablu

New Member

bschwartz

Well-known Member

Bellablu

New Member

Similar threads

Forum statistics

Share this page

Auto Splitting of Data to 2 Worksheets

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock