Share via

Excel - Creating a new table from an existing table

Anonymous
2024-07-19T13:04:24+00:00

Hello all,

I have a very big data set, with thousands of rows and at least 30 columns. From this raw data, I want to create a table with data of interests.

For example, lets consider the below table,

Timestamp A B C D
29-05-2024 00:00:03 1.500366 3.326007 -11.8376 12.304814
29-05-2024 00:00:06 1.500366 3.324542 -11.9231 12.304814
29-05-2024 00:00:11 1.500366 3.326007 -11.8742 12.304814
29-05-2024 00:00:16 1.500366 3.324542 -11.862 12.304814
29-05-2024 00:00:21 1.499634 3.324542 -11.9475 12.302529
29-05-2024 00:00:26 1.500366 3.324542 -11.9109 12.302529
29-05-2024 00:00:31 1.499634 3.326007 -11.9109 12.302529

The first column is time stamp, the complete table will have data from many days.

The new table should be created for a particular day and with only column A and C. The new table should look like below table

Timestamp A C
29-05-2024 00:00:03 1.500366 -11.8376
29-05-2024 00:00:06 1.500366 -11.9231
29-05-2024 00:00:11 1.500366 -11.8742
29-05-2024 00:00:16 1.500366 -11.862
29-05-2024 00:00:21 1.499634 -11.9475
29-05-2024 00:00:26 1.500366 -11.9109
29-05-2024 00:00:31 1.499634 -11.9109

Is there a way to create a complex formula that can be used to do this automatically.

Thank you in advance

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2024-07-19T13:41:46+00:00

You may try choosecols

CHOOSECOLS function - Microsoft Support

=CHOOSECOLS(Sheet1!A1:E8,1,2,4)

Image

If you want special date, you can use this one.

=FILTER(CHOOSECOLS(Sheet1!A2:E8,1,2,4),INT(Sheet1!A2:A8)=DATE(2024,5,29))

Was this answer helpful?

2 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-08-03T14:23:02+00:00

    You may use power query to get data from that range. Power Query will convert the formula result to table. Then you can create graph from that table.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-08-03T04:44:05+00:00

    Is there a workaround method to format the filtered data as a table?

    My goal is to create a graph from the filtered data.

    Any ideas will really help.

    Thank you.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-07-24T13:04:38+00:00

    Array formula cannot be used in Table.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-07-24T10:16:42+00:00

    Hello Snow Lu,

    Thank you for sharing the formulas, it's working.

    However, I faced few problems

    1. If use the formula =FILTER(CHOOSECOLS(_Log713[#All],1,26,58),INT(_Log713[Timestamp])=DATE(2024,6,5)). I am getting the error #VALUE!
    2. If i modify the formula to =FILTER(CHOOSECOLS(Sheet4!A2:EU135148,1,26,58),INT(Sheet4!A2:A135148)=DATE(2024,6,5)). I ignored the first and last line of the table. It's working.
    3. If i modify the formula to =FILTER(CHOOSECOLS(Sheet4!A1:EU135148,1,26,58),INT(Sheet4!A1:A135148)=DATE(2024,6,5)) including the first row (Headers). #VALUE! error is thrown.
    4. If i modify the formula to =FILTER(CHOOSECOLS(Sheet4!A2:EU135149,1,26,58),INT(Sheet4!A2:A135149)=DATE(2024,6,5)) including the first row (Headers). It's working.
    5. If I use formula in a table, #SPILL! error is thrown.

    I want the data from the formula to be formatted as a table. Is there a way to do that?

    Thank you in advance

    Was this answer helpful?

    0 comments