How to select from a list related to another list?

We are trying to automate the working.

I am working now to convert from ETABS to AutoCAD.

output from Etabs is an Excel file ( attached) we want to draw lines and insert text in between 2 points

for example

Text location shout be middpoint of start and end point

Data from Sheet "Connectivity Data"

("string"	"start point"	"End point"	)
("B223"  	"976" 		"975"		)

and points located in another sheet

Data from Sheet "Joint Coordinates"

("LayerName"	"Point ID"	"Ignored Number"	"X"	"Y"		)
("ROOF"		"1284"		"406"			"43.1" 	"1.6896"	)

I started using getexcel and got 2 lists one is text, and another list is points coordenate, but I cant make this link to search in points list to get the point coordenate

 (defun c:e2c ()
 (if
   (setq filepath (getfiled "Select Excel file to read :" (getvar "dwgprefix") "xlsx" )
   (progn
     (setq beams (GetExcel filepath	"Beam Connectivity Data"	"c500"))
     (setq coord (GetExcel filepath	"Joint Coordinates"		"e500"))
     ))
 (setq beam-cnt 3)
 (setq coord-cnt 3)
 
 (While
   (if (and
  (setq beam	(nth beam-cnt beams))
  (setq BeamID	(nth 0 beam))
  (setq BeamS	(nth 1 beam))
  (setq BeamE	(nth 2 beam))

  (setq coord-pnt	(nth coord-cnt coord))
  (setq PntLyr		(nth 0 coord-pnt))
  (setq PntNo		(nth 1 coord-pnt))
  (setq PntX		(nth 2 coord-pnt))
  (setq PntY		(nth 3 coord-pnt))
  )
     (if (= BeamS PntNo)
(progn
  (setq PntLyrS	PntLyr	)
  (setq PntNoS	PntNo	)
  (setq PntXS	PntX	)
  (setq PntYS	PntY	)
  )
(if (= BeamE PntNo)
  (progn
    (setq PntLyrE	PntLyr	)
    (setq PntNoE	PntNo	)
    (setq PntXE		PntX	)
    (setq PntYE		PntY	)
    ))))))

I stucked in hoe to make loop to check the point in coordenate list

I am wondering if some one can help to complete this lisp

0123.xlsx

Assume that you have 2 lists as below:

(setq beam '(("B223" "976" "975"))

coord-pnt '(("975" "55,4" "-20,4604" "6,2") ("976" "49,1" "-20,4604" "6,2")))

You use this code to get the combination list:

(mapcar '(lambda(x) (if (and (setq tmp (assoc (cadr x) coord-pnt))

(setq tmp1 (assoc (caddr x) coord-pnt)))

(list (car x) tmp tmp1))) beam)

Thanx 7o7 for your help

but may be i didnot get it or I did not explain enough.

I have 2 lists,

One of them have a list of strings each string located between 2 points

(("TABLE:  Beam Connectivity Data" "" "")
 ("Beam" "I-End Point" "J-End Point")
 ("" "" "")
 ("B223" "976" "975")
 ("B224" "977" "976")
 ("B225" "978" "977")
 ("B226" "980" "979")
 ("B227" "981" "980")
 ("B228" "982" "981")
 ("B229" "985" "986")
 ("B232" "976" "980")
 ("B233" "980" "985")
...

and another list is coordenate of each point

(("TABLE:  Joint Coordinates" "" "" "" "")
 ("Story" "Label" "Unique Name" "X" "Y")
 ("" "" "" "m" "m")
 ("ROOF" "1283" "404" "43.1" "-1.2104")
 ("ROOF" "1284" "406" "43.1" "1.6896")
 ("ROOF" "1285" "408" "40.2" "1.6896")
 ("ROOF" "1286" "410" "40.2" "-1.2104")
 ("FIRST" "975" "47" "55.4" "-20.4604")
 ("FIRST" "976" "48" "49.1" "-20.4604")
 ("FIRST" "977" "49" "44.9" "-20.4604")
 ("FIRST" "978" "50" "40.2" "-20.4604")
 ("FIRST" "979" "413" "55.4" "-15.9604")
 ("FIRST" "980" "3" "49.1" "-15.9604")
...

What I am trying to do is

select first string "B223" and search for the 2 points "976" "975" in second list

then draw a line between then locate the string in middle of the 2 points.

both of then ( the line and the string) to be in a layer names text next to the point

This ?

(setq lst1 '(("TABLE:  Beam Connectivity Data" "" "")
            ("Beam" "I-End Point" "J-End Point")
            ("" "" "")
            ("B223" "976" "975")
            ("B224" "977" "976")
            ("B225" "978" "977")
            ("B226" "980" "979")
            ("B227" "981" "980")
            ("B228" "982" "981")
            ("B229" "985" "986")
            ("B232" "976" "980")
            ("B233" "980" "985")
           )
     lst2 '(("TABLE:  Joint Coordinates" "" "" "" "")
            ("Story" "Label" "Unique Name" "X" "Y")
            ("" "" "" "m" "m")
            ("ROOF" "1283" "404" "43.1" "-1.2104")
            ("ROOF" "1284" "406" "43.1" "1.6896")
            ("ROOF" "1285" "408" "40.2" "1.6896")
            ("ROOF" "1286" "410" "40.2" "-1.2104")
            ("FIRST" "975" "47" "55.4" "-20.4604")
            ("FIRST" "976" "48" "49.1" "-20.4604")
            ("FIRST" "977" "49" "44.9" "-20.4604")
            ("FIRST" "978" "50" "40.2" "-20.4604")
            ("FIRST" "979" "413" "55.4" "-15.9604")
            ("FIRST" "980" "3" "49.1" "-15.9604")
           )
)
(foreach j (cdr lst1)
 (foreach k (cdr lst2)
   (if (eq (cadr j) (cadr k))
     (setq lst (cons k lst))
   )
 )
)

You should have the final list in the variable "lst" .

With 2 lists lst1 and lst2 as above, you try this:

(defun c:test()
 (setvar 'osmode 0)
 (setq lst2 (mapcar '(lambda(x) (list (nth 1 x) (list (atof (nth 3 x)) (atof (nth  4 x))))) lst2)
lst3 (mapcar '(lambda(x) (if (and (setq tmp (assoc (cadr x) lst2))
				    (setq tmp1 (assoc (caddr x) lst2)))
			    (list (car x) tmp tmp1))) lst1))
 (foreach item lst3
   (if (and (caadr item) (/= "" (caadr item)))
     (progn
       (setq p1 (cadr (cadr item))
    p2 (cadr (last item)))
       (command "line" p1 p2 "")
       (command "text" (polar p1 (angle p1 p2) (* 0.5 (distance p1 p2))) "" "" (car item)))
    )
 )
)

Thanks all for help

I made a slide modfy to match my needs but one more thing

How to use (nth 0 x) in coord as a layername

(defun c:e2c0 ()
 (setvar 'osmode 0)
 (_makefonts '(("SDC-TEXT" . "Arial.ttf")))
 (if
   (setq filepath (getfiled "Select Excel file to read :" (getvar "dwgprefix") "xlsx" )
   (progn
     (setq beams (GetExcel filepath	"Beam Connectivity Data"	"c500"))
     (setq coord (GetExcel filepath	"Joint Coordinates"		"e500"))
     ))
 
 (setq coordd	(mapcar '(lambda(x) (list (nth 1 x) (list (atof (nth 3 x)) (atof (nth  4 x))))) coord))
 (setq lst	(mapcar '(lambda(x) (if (and (setq tmp (assoc (cadr x) coordd))
				     (setq tmp1 (assoc (caddr x) coordd)))
			    (list (car x) tmp tmp1))) beams))
 (foreach item lst
   (if (and (caadr item) (/= "" (caadr item)))
     (progn
       (setq p1 (cadr (cadr item))
      p2 (cadr (last item)))
(makeline p1 p2 lyr)
(makeText (car item) (polar p1 (angle p1 p2) (* 0.5 (distance p1 p2))) lyr 0.25 0 "SDC-TEXT" 1 2)
)
    )
 )
)

You change to this:

 (setq coordd  (mapcar '(lambda(x) (list (nth 1 x) (list (atof (nth 3 x)) (atof (nth  4 x)))  (nth 0 x))) coordd))
 (setq lst	(mapcar '(lambda(x) (if (and (setq tmp (assoc (cadr x) coordd))
				     (setq tmp1 (assoc (caddr x) coordd)))
			    (list (car x) tmp tmp1))) beams))
 (foreach item lst
   (if (and (caadr item) (/= "" (caadr item)))
     (progn
       (setq p1 (cadr (cadr item))
      p2 (cadr (last item))
      lyr (last (last item)))
(makeline p1 p2 lyr)
(makeText (car item) (polar p1 (angle p1 p2) (* 0.5 (distance p1 p2))) lyr 0.25 0 "SDC-TEXT" 1 2)
)
    )
 )

#Be careful about the name coordd or coord.

Thanx 7o7

Thanx Tharwat

Exact what I am looking for.

You are welcome!

# Don't be sorry about your English.