Jun 26 2021 01:40 AM
Sample data only:
Table 1
Pls help what formula to use if data from table 1 be consolidate in the table 2 using formula
- how to generate the count of FSS/PSS/RSM/and so on who are under quarantine/RT-PCR+/and so on to table1 (data entry).
Table 2
Thank you.
Jun 26 2021 02:57 AM
It looks like job for Power Query if transform entire table. If one by one perhaps SUMIFS() works. But it's hard to be more concrete without sample file and knowing on which version of Excel you are.
Jun 26 2021 06:37 AM
Jun 26 2021 08:20 PM
Hi @Malen0125 ,
Ang imo table ge convert nko using power query from this:
to this:
nya gamit ko ug ani na formula:
=IF(SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3)=0,"",SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3))
sa table 2
kung naa ka pangutana pm lang dire
file is attached
cheers
Jun 27 2021 04:12 AM
Hi, @Yea_So
Wow! this is great. Thank you so much.
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
Jun 27 2021 04:24 AM
Hi, @Ravitosh Kumar
Yes, i can share it with you. Pls see attached document. hope to hear from you soonest.
Jun 27 2021 06:51 AM
Jun 27 2021 07:15 AM
Jun 27 2021 09:15 AM - edited Jun 27 2021 10:43 AM
SolutionHi @Malen0125,
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
A. Dili necessary pero it will make it mas sayon. I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model. Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.
Jun 27 2021 10:35 AM - edited Jun 27 2021 10:36 AM
@Malen0125 i have converted data entry sheet to table. worked file attached.
=SUMPRODUCT((Table1[Designation]=E$5)*(Table1[[#Headers],[Anti-Body + / Antigen +]:[w/ FTW]]=$C10)*Table1[[Anti-Body + / Antigen +]:[w/ FTW]])
summary data shows & works fine with additional data i tested.
it is intersection of all the 3 conditions: (designation) with (type of test) with (data values).
Jun 27 2021 04:55 PM
Hi, @Yea_So
This is Great! i learn something new from excel with you, using various formula na hindi ko na gamit.
Thank you so much for sharing your knowledge in excel. Talk to you soon.
Jun 27 2021 04:58 PM
Jun 29 2021 02:30 AM
Jun 27 2021 09:15 AM - edited Jun 27 2021 10:43 AM
SolutionHi @Malen0125,
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
A. Dili necessary pero it will make it mas sayon. I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model. Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.