database -- sort question

J

janus67

Board Regular
Joined
Nov 26, 2006
Messages
144
Office Version
  1. 2016
Platform
  1. Windows
sorting problem

I'm interested in Astrology and have a database of planetary longitude positions in Excel

I'd like to be able to scan the database to flag when one or more planets
make angles to others

1/ set scan for line 6913-6943 ( dates)
so ColB is 90degrees to ColC - no = 0
ColB is 90degrees to Col D - yes = 1 ( mark it somehow , colour cell etc)
etc to Col L

2/ could the scan be for various degrees in the one scan
or as a separate scan for each degree set




is this easier to do in Access or leave it in Excel?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
ok, finally figured that out
xlam inserted -- hope it works out ok

Book1
ABCDEFGHIJKLMNOPQR
1sunmercuryvenusmarsjupitersaturnuranusneptuneplutonode
231.03.201819118858237225.4273.2128.3344.11289.49259.57
301.04.201819219260238225.5273.2428.31344.12289.5259.63here ColC passed ColB some indication of match
405.04.201819620666240225.8273.3628.35344.14289.52259.88
506.04.201819720968240225.9273.3928.36344.15289.52259.94here Col B passed 180 deg from Col H
609.04.201820021873242226.1273.4828.4344.17289.54260.13
710.04.201820122275242226.2273.5128.41344.17289.54260.19
811.04.201820222576243226.3273.5428.42344.18289.55260.25here ColC passed @ 120d angle to Col I
9
101/so is it easier/simpler to do this in Excel or Access
112/ I'd like to be able to choose the sort periodie 1-15th Octoberor could I just "select" the sort period
122/ can the sort be sequential ie ColA against the rest ( B-K) then ColB against the rest ( C-K) etc
Sheet1
 
Upvote 0
I had to delete some days to fit all in
basically it's a day per line -- I 'll try again to get it all in
 
Upvote 0
ok here it is in full
Book1
ABCDEFGHIJKLMNOPQR
1sunmercuryvenusmarsjupitersaturnuranusneptuneplutonode
229.03.201818918055236225.3273.1528.28344.1289.48259.45
330.03.201819018457237225.3273.1828.29344.11289.49259.51
431.03.201819118858237225.4273.2128.3344.11289.49259.57
501.04.201819219260238225.5273.2428.31344.12289.5259.63here ColC passed ColB some indication of match
602.04.201819319562238225.6273.2728.32344.12289.5259.7
703.04.201819419963239225.6273.328.33344.13289.51259.76
804.04.201819520265239225.7273.3328.34344.14289.51259.82
905.04.201819620666240225.8273.3628.35344.14289.52259.88
1006.04.201819720968240225.9273.3928.36344.15289.52259.94here Col B passed 180 deg from Col H
1107.04.201819821270241226273.4228.38344.15289.53260
1208.04.201819921571241226273.4528.39344.16289.53260.06
1309.04.201820021873242226.1273.4828.4344.17289.54260.13
1410.04.201820122275242226.2273.5128.41344.17289.54260.19
1511.04.201820222576243226.3273.5428.42344.18289.55260.25here ColC passed @ 120d angle to Col I
1612.04.201820322778244226.3273.5728.43344.18289.55260.31
1713.04.201820423079244226.4273.628.44344.19289.56260.37
1814.04.201820523381245226.5273.6328.45344.2289.56260.43
1915.04.201820623683245226.6273.6728.46344.2289.57260.49
20
211/so is it easier/simpler to do this in Excel or Access
222/ I'd like to be able to choose the sort periodie 1-15th Octoberor could I just "select" the sort period
232/ can the sort be sequential ie ColA against the rest ( B-K) then ColB against the rest ( C-K) etc
Sheet1
 
Upvote 0
Hi, I taken your case. Tell me how do you obtain this data? Manually, some database, etc? manually, from some database, etc
 
Upvote 0
For me, conditional formatting is not a good way, because a field can be affected, by more than one condition. But, an easy way, would be use additional columns or Excel tables (Ctrl +t), I don't know what is your Excel level. Really, there is a lot of ways to mke this

An idea a book with three sheets:

Book1
ABCDEFGHIJKLMNOPQR
1Datessunmercuryvenusmarsjupitersaturnuranusneptuneplutonodesun- mercurymercury- uranusmercury- neptune
229.03.201818918055236225.3273.1528.28344.1289.48259.45000
330.03.201819018457237225.3273.1828.29344.11289.49259.51000
431.03.201819118858237225.4273.2128.3344.11289.49259.57000
501.04.201819219260238225.5273.2428.31344.12289.5259.63100
602.04.201819319562238225.6273.2728.32344.12289.5259.7000
703.04.201819419963239225.6273.328.33344.13289.51259.76000
804.04.201819520265239225.7273.3328.34344.14289.51259.82000
905.04.201819620666240225.8273.3628.35344.14289.52259.88000
1006.04.201819720968240225.9273.3928.36344.15289.52259.94020
1107.04.201819821270241226273.4228.38344.15289.53260020
1208.04.201819921571241226273.4528.39344.16289.53260.06020
1309.04.201820021873242226.1273.4828.4344.17289.54260.13020
1410.04.201820122275242226.2273.5128.41344.17289.54260.19020
1511.04.201820222576243226.3273.5428.42344.18289.55260.25020
1612.04.201820322778244226.3273.5728.43344.18289.55260.31020
1713.04.201820423079244226.4273.628.44344.19289.56260.37020
1814.04.201820523381245226.5273.6328.45344.2289.56260.43020
1915.04.201820623683245226.6273.6728.46344.2289.57260.49020
20Your inquiryresult test
21here ColC passed @ 120d angle to Col I119.18hernan: Check ths results because is not equal or greater than 120
22here ColC passed ColBsome indication of match
23here Col B passed 1 80 deg from Col H180.64
24
251/so is it easier/simpler to do this in Excel or AccessI prefer Access (but knowing the whole project), but it is also possible to do it with Excel with Power Query
262/I'd like to be able to choose the sort period: ie 1-15th October or could I just "select" the sort periodYes, you can
272/can the sort be sequentialie ColA against the rest ( B-K) then ColB against the rest ( C-K) etcYes, you can
Data
Cell Formulas
RangeFormula
L2:L19L2=IF([@sun]=[@mercury],1,0)
M2:M19M2=IF(([@mercury]-[@uranus])>=180,2,0)
N2:N19N2=IF(([@mercury]-[@neptune])>=120,3,0)
Q21Q21=I15-C15
Q23Q23=C10-H10


Messages table, permit to you coding all and let you keep in mind on number, this number will be easy to remember, for you, as time passes
Book1
ABCD
1message_idmessage_description
2smsun match mercury
32mercury passed uranus
43neptune passed mercury
5
6
7
8
Messages


Book1
ABCDEFG
1Datesm23456
229.03.2018   
330.03.2018   
431.03.2018   
501.04.2018sun match mercury  
602.04.2018   
703.04.2018   
804.04.2018   
905.04.2018   
1006.04.2018 mercury passed uranus 
1107.04.2018 mercury passed uranus 
1208.04.2018 mercury passed uranus 
1309.04.2018 mercury passed uranus 
1410.04.2018 mercury passed uranus 
1511.04.2018 mercury passed uranus 
1612.04.2018 mercury passed uranus 
1713.04.2018 mercury passed uranus 
1814.04.2018 mercury passed uranus 
1915.04.2018 mercury passed uranus 
Resume
Cell Formulas
RangeFormula
B2:B19B2=IF(tblData[@[sun- mercury]]="sm",INDEX(messages,MATCH($B$1,messages[message_id],0),2),"")
C2:C19C2=IF(tblData[@[mercury- uranus]]=2,INDEX(messages,MATCH($C$1,messages[message_id],0),2),"")
D2:D19D2=IF(tblData[@[mercury- neptune]]=3,INDEX(messages,MATCH($C$1,messages[message_id],0),2),"")


Here, I share an Example
 
Last edited:
Upvote 0
hernantorres23 said:
Hi, I taken your case. Tell me how do you obtain this data? Manually, some database, etc? manually, from some database, etc
Click to expand...

Hello, I get the raw data by downloading from an Ephemeris program directly into Excel
 
Upvote 0
hernantorres23 said:
For me, conditional formatting is not a good way, because a field can be affected, by more than one condition. But, an easy way, would be use additional columns or Excel tables (Ctrl +t), I don't know what is your Excel level. Really, there is a lot of ways to mke this

An idea a book with three sheets:

Book1
ABCDEFGHIJKLMNOPQR
1Datessunmercuryvenusmarsjupitersaturnuranusneptuneplutonodesun- mercurymercury- uranusmercury- neptune
229.03.201818918055236225.3273.1528.28344.1289.48259.45000
330.03.201819018457237225.3273.1828.29344.11289.49259.51000
431.03.201819118858237225.4273.2128.3344.11289.49259.57000
501.04.201819219260238225.5273.2428.31344.12289.5259.63100
602.04.201819319562238225.6273.2728.32344.12289.5259.7000
703.04.201819419963239225.6273.328.33344.13289.51259.76000
804.04.201819520265239225.7273.3328.34344.14289.51259.82000
905.04.201819620666240225.8273.3628.35344.14289.52259.88000
1006.04.201819720968240225.9273.3928.36344.15289.52259.94020
1107.04.201819821270241226273.4228.38344.15289.53260020
1208.04.201819921571241226273.4528.39344.16289.53260.06020
1309.04.201820021873242226.1273.4828.4344.17289.54260.13020
1410.04.201820122275242226.2273.5128.41344.17289.54260.19020
1511.04.201820222576243226.3273.5428.42344.18289.55260.25020
1612.04.201820322778244226.3273.5728.43344.18289.55260.31020
1713.04.201820423079244226.4273.628.44344.19289.56260.37020
1814.04.201820523381245226.5273.6328.45344.2289.56260.43020
1915.04.201820623683245226.6273.6728.46344.2289.57260.49020
20Your inquiryresult test
21here ColC passed @ 120d angle to Col I119.18hernan: Check ths results because is not equal or greater than 120
22here ColC passed ColBsome indication of match
23here Col B passed 1 80 deg from Col H180.64
24
251/so is it easier/simpler to do this in Excel or AccessI prefer Access (but knowing the whole project), but it is also possible to do it with Excel with Power Query
262/I'd like to be able to choose the sort period: ie 1-15th October or could I just "select" the sort periodYes, you can
272/can the sort be sequentialie ColA against the rest ( B-K) then ColB against the rest ( C-K) etcYes, you can
Data
Cell Formulas
RangeFormula
L2:L19L2=IF([@sun]=[@mercury],1,0)
M2:M19M2=IF(([@mercury]-[@uranus])>=180,2,0)
N2:N19N2=IF(([@mercury]-[@neptune])>=120,3,0)
Q21Q21=I15-C15
Q23Q23=C10-H10


Messages table, permit to you coding all and let you keep in mind on number, this number will be easy to remember, for you, as time passes
Book1
ABCD
1message_idmessage_description
2smsun match mercury
32mercury passed uranus
43neptune passed mercury
5
6
7
8
Messages


Book1
ABCDEFG
1Datesm23456
229.03.2018   
330.03.2018   
431.03.2018   
501.04.2018sun match mercury  
602.04.2018   
703.04.2018   
804.04.2018   
905.04.2018   
1006.04.2018 mercury passed uranus 
1107.04.2018 mercury passed uranus 
1208.04.2018 mercury passed uranus 
1309.04.2018 mercury passed uranus 
1410.04.2018 mercury passed uranus 
1511.04.2018 mercury passed uranus 
1612.04.2018 mercury passed uranus 
1713.04.2018 mercury passed uranus 
1814.04.2018 mercury passed uranus 
1915.04.2018 mercury passed uranus 
Resume
Cell Formulas
RangeFormula
B2:B19B2=IF(tblData[@[sun- mercury]]="sm",INDEX(messages,MATCH($B$1,messages[message_id],0),2),"")
C2:C19C2=IF(tblData[@[mercury- uranus]]=2,INDEX(messages,MATCH($C$1,messages[message_id],0),2),"")
D2:D19D2=IF(tblData[@[mercury- neptune]]=3,INDEX(messages,MATCH($C$1,messages[message_id],0),2),"")


Here, I share an Example
Click to expand...
thanks, i'll have a look at it
 
Upvote 0
first thanks for your help so far

my level of Excel is reasonably basic
I don't use it a lot & over the years have learned to do what I need
some time ago , I opened up the ephemeris data to make separate info on Page 2 referring to the database on Page 1 ( to me, complex)
I try to avoid complexity
what I had in mind ( simplistic I know) is
1/ highlight a month of data ahead
2/ drop the formula on to look for where Col1 is ( >=,0, 90deg, 120d, 180d, 240d, 270d ) from Col2, then Col3 etc
however you prefer to do it ( I'd like to be able to edit it )

3/ results could be on the same line or as you have, a results page ( resume)

4/ results should be -- sun 90 moon, sun 180 venus , etc ( maybe I can edit this )

5/ do we really need the Message page at all ?

6/ the message on Resume needs only show one line

thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Excel user form - general questions
Replies
2
Views
1K
mikerickson
mikerickson
P
  • Question
Error: Application-defined or Object-defined error
Replies
1
Views
1K
Pinkster69
P
J
  • Question
I need help speeding up a macro
Replies
2
Views
662
AlphaFrog
AlphaFrog
C
  • Question
Visual Basic Code - Cannot add more times to code.
Replies
4
Views
1K
civilred
C
J
  • Question
Excel 4 cell match..any improvements?
Replies
6
Views
562
JimmyBob
J

Forum statistics

Threads
1,214,998
Messages
6,122,643
Members
449,093
Latest member
Ahmad123098

Share this page

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
Back
Top