Formula or VBA to aggreagate data from table?

Hi,

how can i do aggregation based on chosen values from table?
It is better to use code or formula?

In source there is a source table.
and want i want is to to get Result string:

Screenshot_27.png

So basically for Server1 and Location1 i have 3 numbers = 1,2 and 3 and i want get them in column Result in example1 worksheet.

I think formula would be better here. Because VBA demands to add worksheet change event and i have a lot conditions there right now.

Please help,
Jacek

thank you Ali!

Sounds interesting. Disadvantage of this solution is that i still have to trigger refreshing PQ after changing cells.
So i need to implement VBA...
The same will be with pivot table (if it is possible?)

What about formula?
It is possible?

Jacek

ok thank you Ali.

Can you provide also formula for this?
With aggregation?

I will test both.

Jacek

Put this formula in cell D2 of the Source sheet:

=IF(AND(Table1[[#This Row],[Server]]=Example1!$A$2,Table1[[#This Row],[LocationID]]=Example1!$B$2),MAX(D$1:D1)+1,"-")

It should automatically copy itself down and add that column to your table.

Then you can use this formula in cell E2 of the Example1 sheet:

=IFERROR(INDEX(Source!$C:$C,MATCH(ROWS($1:1),Source!$D:$D,0)),"")

Copy this down as far as you think you will need.

Hope this helps.

Pete

Hi Pete,

thank you. Very interesting approach. It is possible to create this helpep column separated from table?

Best,
Jacek

You can put it in whichever column you like (or even on another sheet), but you will need to amend the second formula so that it points to the new column instead of Source!$D:$D.

Hope this helps, and thanks for the rep.

Pete

Okay, I've moved the helper column to column E in the Source sheet. Is that what you wanted?

Hope this helps.

Pete

ok thank you Pete_UK.

Yes it was straightforward but what if you want to put the list for example in different sheet and different column and cell?:
Attachment 638803

it will not work.
And i can not do this in the same table and same horizontal level as table is....

Can you please with writing aggregate formula ?

Jacek

Your latest attachment comes up as invalid when I try to download it. Did you try to use the Paperclip icon? It doesn't work on this forum, so use Manage Attachments from the Go Advanced button below the edit window, and follow the on-screen prompts.

Hope this helps.

Pete

Ok,

once again:

Screenshot_2.png


i simply moved it in another place in workbook. I do not want to have this as the same level of Table.

Jacek

I've moved the helper column into another sheet and made appropriate changes to the formulae.

Hope this helps.

Pete

o wow thank you!!!

i tried by my own with this one but i failed:

=IF(AND(Table1[Server]=Example!A2;Table1[LocationID]=Example!B2);MAX(E$1:E1)+1;"-")

This is very nice but:
1. If i will move my table i will have an issue - because i will not know from where should i start comparing table rows.
I think that i should have reference somehow to dynamic table instead of having addresses.
2. I think that it should use row reference but i can not (i do not know) how to implement this...

please help,
Jacek

Hi,

anyone can figure out formula for this ? (for table in different places in workbook).

Best,
Jacek

Hi,

please help.
No formula to aggregate this using reference to table headers and columns?

Jacek

I really don't understand what the problem is here. It might be better to attach a new workbook with some realistic examples of how you intend to use it. Are you really going to move the table around after setting it up?

Pete

Hi Pete,

i moved table and i am attaching workbook.
Thank you for help and support.

So question is, how to refer to the table if table will be moved? (and i have the case while loading table from source - the position of tables can be changed)

Jacek

Screenshot_6.png

Sorry for the delay in getting back to you. My ISP chose today to "update their infrastructure" and so the internet connection went down soon after my earlier post - at least they gave us some warning, but it has been frustrating nonetheless.

I thought you wanted the helper column in a different sheet, so that it doesn't interfere with the Source sheet, so I have used column D in the Example sheet with this formula in D2:

=IFERROR(IF(AND(INDEX(Table1,ROWS($1:1),1)=$A$2,INDEX(Table1,ROWS($1:1),2)=$B$2),MAX(D$1:D1)+1,"-"),"-")

which can be copied down as far as you like. You can interrogate Table1 using the INDEX function, as "Table1" is just a named range (well, it's a bit more than that, but you can see it listed in Name Manager). The ROWS($1:1) term increments on each row that the formula is copied to, and the first INDEX term is looking specifically in column 1 of the table (shown in red) and the second one at column 2. The IFERROR term allows you to copy down beyond the number of rows in the table, in order to accommodate more data being added to the table at a later date.

I've put the Result column in column G, and used this formula in G2:

=IFERROR(INDEX(Table1,MATCH(ROWS($1:1),$D:$D,0)-1,3),"")

This is very similar to before, but now we are getting the data from the 3rd column of Table1. Although these column numbers have been hard-coded in the formulae, you could easily use a MATCH function acting on the column headers.

If you move your Table1 around by inserting/deleting rows or columns in the Source sheet, the formulae should still bring the same data back.

Hope this helps.

Pete

o wow you are the boss!!

thank you once again for help and support.

Jacek

I'm glad that we finally got to a solution that works for you.

Thanks again for the reps.

Pete