User not logged in - login - register
Home Calendar Books School Tool Photo Gallery Message Boards Users Statistics Advertise Site Info
go to bottom | |
 Message Boards » » Excel Help Page [1]  
wdprice3
BinaryBuffonary
45800 Posts
user info
edit post

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

dtownral
Suspended
26632 Posts
user info
edit post

are table functions okay?

2/20/2018 9:18:55 AM

wdprice3
BinaryBuffonary
45800 Posts
user info
edit post

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

darkone
(\/) (;,,,;) (\/)
11356 Posts
user info
edit post

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

Oh, wait... Excel.

2/20/2018 12:17:05 PM

qntmfred
retired
39922 Posts
user info
edit post

can you use google sheets instead of excel?

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

2/20/2018 12:46:15 PM

wdprice3
BinaryBuffonary
45800 Posts
user info
edit post

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

dtownral
Suspended
26632 Posts
user info
edit post

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

moron
All American
32566 Posts
user info
edit post

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
...


Is there a way in excel or SQL to have each category its own column? Essentially want to make a stacked bar chart from the data

[Edited on March 30, 2020 at 10:44 PM. Reason : ]

3/30/2020 10:43:43 PM

qntmfred
retired
39922 Posts
user info
edit post

TRANSPOSE(UNIQUE(C2:C))

3/30/2020 11:49:00 PM

justinh524
HumanSkinEggBaby
21558 Posts
user info
edit post

just ask Clippy

3/31/2020 1:23:43 AM

moron
All American
32566 Posts
user info
edit post

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

3/31/2020 4:32:37 PM

 Message Boards » Tech Talk » Excel Help Page [1]  
go to top | |
Admin Options : move topic | lock topic

© 2020 by The Wolf Web - All Rights Reserved.
The material located at this site is not endorsed, sponsored or provided by or on behalf of North Carolina State University.
Powered by CrazyWeb v2.37 - our disclaimer.