creating table

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
I would like to create a table with information i have in two columns.

The issue I have is that there are rows that do not have information and i do not want to include that in the table.

The following screenshots illustrate the appearance of the data set and what i would like it to look like.


The current data set
1600183831306.png


What i would like to create
1600183964904.png


Really appreciate the help.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
What about filtering on column F or H?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCDEFGHI
1
231/10/2015-1.412558921Saturday 01/11/2015-1.386801
301/11/2015-1.36104291Sunday-1.38680091508/11/2015-1.997973
402/11/2015-2.629211911Monday 15/11/2015-2.232602
503/11/2015-1.396562134Tuesday 22/11/2015-2.241532
604/11/2015-1.831855859Wednesday 29/11/2015-2.131678
705/11/2015-2.799223002Thursday 06/12/2015-2.091476
806/11/2015-1.872631499Friday 13/12/20150
907/11/2015-1.277691243Saturday 20/12/20150
1008/11/2015-2.178635784Sunday-1.99797306227/12/20150
1109/11/2015-2.921114103Monday 03/01/20160
1210/11/2015-2.680514736Tuesday 
1311/11/2015-1.036175935Wednesday 
1412/11/2015-1.244940138Thursday 
1513/11/2015-2.695439554Friday 
1614/11/2015-2.664679119Saturday 
1715/11/2015-2.385352769Sunday-2.232602336
1816/11/2015-2.702402518Monday 
1917/11/2015-1.861110877Tuesday 
2018/11/2015-2.825728753Wednesday 
2119/11/2015-2.601115017Thursday 
2220/11/2015-2.432868797Friday 
2321/11/2015-1.648590788Saturday 
2422/11/2015-1.618909957Sunday-2.241532387
2523/11/2015-1.782181522Monday 
2624/11/2015-2.876539358Tuesday 
2725/11/2015-1.58492922Wednesday 
2826/11/2015-2.321220428Thursday 
2927/11/2015-2.081107275Friday 
3028/11/2015-2.538257542Saturday 
3129/11/2015-1.73750879Sunday-2.131677733
3230/11/2015-1.472958623Monday 
3301/12/2015-2.709993854Tuesday 
Data
Cell Formulas
RangeFormula
E2:E33E2=TEXT(A2,"dddd")
F2:F33F2=IF(E2="Sunday",AVERAGE(FILTER($B$2:$B$33,ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(A2))),"")
H2:H11H2=SEQUENCE(10,,WORKDAY.INTL(A2-1,1,"1111110"),7)
I2:I11I2=AVERAGE(FILTER($B$2:$B$33,ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(H2),0))
Dynamic array formulas.
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
That works!! Thank you!!

What formulas would I use to get data from column C for the same time period and I wanted to create a table from the data i have in column k - ADP qtrly average.

Sorry for so many questions.

I really appreciate the help!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just use the same formula as I2 but change the filter range from B to C
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for the help.

one issue i am running into is that i have data that goes to row 25,000

when i change the formula in i2 and change the range from 33 to 25,000 the formula does not work.

what else would i need to change.

thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I grabbed the wrong formula from you previous thread it should be
Excel Formula:
=AVERAGE(FILTER($B$2:$B$25000,(ISOWEEKNUM($A$2:$A$25000)=ISOWEEKNUM(H2))*(YEAR($A$2:$A$25000)=YEAR(H2))))
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Thank you for the help.

what formula would i use for creating a similar table with information from column k - ADP qtrly average.

thank you very much!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
You can use the formula you already have. Just list the relevant dates & point the formula at that.
 

whynot

Board Regular
Joined
Jun 27, 2009
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Got it. Thank you.

what formula would i use to only have the dates that are applicable for each quarter - 3/31/1960, 6/30/1960, 9/30/1960,12/31/1960, ....... 3/31/20, 6/30/20

Thank you again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top