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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Title
[/TD]
[TD]Category
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]01
[/TD]
[TD]Book one
[/TD]
[TD]DDD
[/TD]
[TD]=IF(C2="AAA",1,0)
[/TD]
[/TR]
[TR]
[TD]02
[/TD]
[TD]Book two
[/TD]
[TD]AAA
[/TD]
[TD]=IF(C3="AAA",D1+1,D1)
[/TD]
[/TR]
[TR]
[TD]03
[/TD]
[TD]Book Six
[/TD]
[TD]FFF
[/TD]
[TD]=IF(C3="AAA",D2+1,D2)
[/TD]
[/TR]
[TR]
[TD]04
[/TD]
[TD]Book three
[/TD]
[TD]AAA
[/TD]
[TD]=IF(C3="AAA",D3+1,D3)
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Title
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Book two
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Book three
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 394, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Title
[/TD]
[TD]Category
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Book one
[/TD]
[TD]DDD
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]Book two
[/TD]
[TD]AAA
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]Book Six
[/TD]
[TD]FFF
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]Book three
[/TD]
[TD]AAA
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]




Sheet2

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

[TABLE="width: 327, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Title
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Book two
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Book three
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Book Six
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 500"]
<tbody>[TR]
[TD]AAA
[/TD]
[TD]Title
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Book two
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Book Three
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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