Trying to find an easy, quick way to extract some data simply by pasting in a few columns and having the other columns extract via functions (no vba, pivot tables, etc.).

Copy in data to Cols A and B. Manually select rows to be deleted via inserting X under Col C. Col D and E extract remaining data.

Pretty much want this:



Pretty sure I've done this before, just don't know how.

2/20/2018 9:13:22 AM

are table functions okay?

2/20/2018 9:18:55 AM

meaning?

trying to get to something as simple as pasting the 2 columns and then inserting Xs and that's all

2/20/2018 9:25:22 AM

E = A(C~='X');

Oh, wait... Excel.

2/20/2018 12:17:05 PM

can you use google sheets instead of excel?

=query(A:C,"select A, B where C<>'X'")

2/20/2018 12:46:15 PM

finally figured it out. well, i did the inverse actually. so not clean, but it works. and only because my data is in ascending order

{=SMALL(IF(C2:C10)="",A2:A10),ROW(INDRECT("1:"&COUNTIF(C2:C10,""))))}

then I just used an index lookup to return Col B

2/20/2018 1:44:12 PM

Quote :

"meaning? "

sorry didn't meant functions, meant formatting

because the stupid easy way is to just format as a table and uncheck x


EDIT:
^way cleaner than my formula

[Edited on February 20, 2018 at 2:22 PM. Reason : .]

2/20/2018 1:57:17 PM

If I have data in this format:

DATE     VALUE     CATEGORY
day1       val       A
day1       val       B
day1       val       C
day1       val       D
day2       val       A
day2       val       D
day2       val       G
...

3/30/2020 10:43:43 PM

TRANSPOSE(UNIQUE(C2:C))

3/30/2020 11:49:00 PM

just ask Clippy

3/31/2020 1:23:43 AM

someone on stack overflow pointed me to the "pivot" SQL function that solved the problem for me

3/31/2020 4:32:37 PM