An Array to track data into a new tab

[Runawaygeek]

Hi guys,

I had a formula, that would copy data from a cell to another sheet, based on a value in another cell within the Row. To do this, I had a count formula on the end, that added 1 each time the trigger value was met. But the first cell was its own formua as i needed the option of a 0

=IF(C2="AAA",1,0)
=IF(C3="AAA",D1+1,D1)

So my data would be something like this:

ID	Title	Category	Count
01	Book one	DDD	=IF(C2="AAA",1,0)
02	Book two	AAA	=IF(C3="AAA",D1+1,D1)
03	Book Six	FFF	=IF(C3="AAA",D2+1,D2)
04	Book three	AAA	=IF(C3="AAA",D3+1,D3)

<tbody>
</tbody>

in count i would get 0, 1, 1, 2
If row 04, had another category, then i would get 0,1,1,1

All this is sheet 1

Then in sheet two, where i would want to return just Rows 2 and 3 all neatly packed up the top.

Title	Count
Book two	1
Book three	2

<tbody>
</tbody>

I have forgotten the formula that would allow me to pack it up, If I do a basic Index and Match, it only works if I have blank rows included.

Anyone got any ideas what i was on about, i know it worked, just forgot. I also am pretty sure it was an array formula, (ctrl shift enter)
I also realise that if i want the other Categories on new sheets, i would need to add other count columns, when i did this last time i have 6 of these.

Thanks for any help,
Ben

 

[admiral100]

Hi,

Try this:-

Sheet1

D2 = 1
D3 Ctrl+Shift+Enter Not just Enter and drag down
=IF(COUNT(SEARCH(C3,$C$2:C3))=0,1,COUNT(SEARCH(C3,$C$2:C3)))

	A	B	C	D
1	ID	Title	Category	Count
2	1	Book one	DDD	1
3	2	Book two	AAA	1
4	3	Book Six	FFF	1
5	4	Book three	AAA	2

<tbody>
</tbody>

Sheet2

B2 =INDEX(Sheet1!$D$2:$D$5,MATCH(A2,Sheet1!$B$2:$B$5,0))

	A	B
1	Title	Count
2	Book two	1
3	Book three	2
4	Book Six	1

<tbody>
</tbody>

 

[Runawaygeek]

Hi

Thank you for getting back to me.
The issue with your method is that count, which i grant is more of a running count, is out.

In sheet one, D1 may well be a 0 as its a count of the values in C
In sheet two, B, needs to be 1, 2, 3 and so on..

Thank you for your help,
Ben

 

[Runawaygeek]

So i remembered my array to bring back the Text aspect of the main sheet, it was just a simple Index and Match, with two criteria.

=INDEX('Sheet1'!B:B,MATCH($A$1&A2,Sheet1'!C:C&'Sheet1'!D:D,0))

(Remeber to press Ctrl+Shift+Enter)

Also, A1 would be the Category Match "AAA", "BBB"

I just need to work out how to get the numbers from Column D to list on the other sheet with no blank rows.

 

[admiral100]

Your formula is not correlated with your data in post 1...
Can you please upload a file with the two sheets ?

 

[Runawaygeek]

Ok, I worked out the number column as well, another array, so Ctrl+Shift+Enter

=IFERROR(INDEX('Sheet1'!B:B,SMALL(IF('Sheet1'!C:C=$A$1,ROW('Sheet1'!D:D)),ROW()-ROW($A$2)+1)),"")

So now i get a build on new a sheet of

AAA	Title
1	Book two
2	Book Three

<tbody>
</tbody>

Happy to pass on a workbook if anyone wants one, let me know.

Thanks all
B

 

[Runawaygeek]

I dont know how to add a work book to this site, but am happy to email you one, have put my email to you in a Direct Message.

 

[Runawaygeek]

Hi Admiral100

I have put a sample file here: https://www.dropbox.com/s/54p4bn9tplg8g0b/Sample.xlsx?dl=0

Hope it makes sense.

Thanks,
Ben

 

[admiral100]

Now everything is clear , glad you success