Copy data from Excel same row different column saving result into KM Variable?

Questions & Suggestions
, ,
1

How can I copy data from a cell in the same row with a matching Column name on the same row and save the result into KM as a Variable. Is this possible if so how? e.g. clicked on cell C4 want to search the row (4) for a Column (at the top of the sheet) for a matching title 'Name' and save the data in the cell to a KM variable. The macro would automatically search the Column names for matching 'Name' and save the result in row 4 of Column 'Name' into a KM Variable. for example Column select C has a title DOB. Now I want to extract the data in Column 'Name' for row 4 which is selected.

Is this possible if so how?

2

You didn't say which spreadsheet app you were using. E.g., Numbers? Excel? Something else?

You didn't seem to say where you got 'Name' from... was it from cell C4 in your example or from somewhere else?

I'm not sure I can follow this without a full, detailed example. Maybe someone else can help you with the explanation you gave, but I don't think I can, unless you provide a fuller example. Please show an actual spreadsheet page, before and after the work is done.

Are you skilled with spreadsheet "formulas"? (Or Excel's Visual Basic?) You may need to use them in order to solve some of your problem. I don't think KM would be very good at "searching a row for a cell with a specific string."

3
  1. Excel
  2. The name of the entire Column, title of the column

e.g. Column A is named Name, Column B is named DOB, Column C is named Address

If I have clicked on Column C (Address) row 4, I want to be able to save the result for A4 as a variable. KM will search for the Column with the matching Column name 'Name'. Column 'Name' could be Column A or even AB.

Is this helpful?

4

I must be getting old because I require a before and after image for something this complicated. Maybe there are younger people here who can help you without seeing a before and after image.

Do you already have a keyboard sequence that you use to solve this problem manually? Or do you not even have a manual solution for this problem yet?

Are you familiar with Visual Basic for Excel? It seems like that's a more direct tool for solving this.

1 Like
5

Hey I'm old and I think I understand :smile:

Here's what I think you're asking for. You want this macro to work in various worksheets so the location of the Name column is not predictable. So the macro needs to figure out the first column that has the word "Name" (and only the word "Name") in Row 1 (where a typical column header is stored). And then move to that column on whatever row you started out in. And then do a Copy and return to the cell you started in. This may or may not be doable in KM but I agree with Sleepy: this seems much more suitable to VB in Excel.

Once you copy the name what will you do with it? You might find it easier to issue a VLOOKUP from wherever you are going to paste the name?

1 Like
6

Fomulas might work, and they would work in either spreadsheet app, but the way he describes it I think he may need a macro, either a VB macro or KM macro. KM could potentially work in either spreadsheet app, but until I really understand the problem I can't write a KM macro. VB would definitely be able to handle something like this, but I can't really help him with that since I don't have VB.

I believe when Excel first came out for MacOS, it didn't come with VB, but I believe it is part of Excel for MacOS now.

7

I am trying to save the result in the cell to KM as a Variable automatically to move onto the next action which needs to run in KM.

8

VB would be the way to go I'm pretty sure. I'm not a VB expert but found the following code here to get you partway there. This would figure out the column and your macro would then move to that column in the same row you're already in and copy the cell in question, then KM can work with the clipboard from there.

I've written some pretty spiffy KM macros for Excel, and could figure out how to get the column number, but the problem would be figuring out the current row number, not sure how I'd do that without using VB (or OCR I guess).

Option Explicit

Sub Sample()
    Dim strSearch As String
    Dim aCell As Range

    strSearch = "Name"

    Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address
    End If
End Sub
9

I typically extract data from Excel using AppleScript and the clipboard. You can assign the extracted data to a KM variable from the clipboard for further processing. There are other ways you could assign the data to a KM variable within the Applescript itself, but I never needed to do that. Here is an example of the script. You can change it to match your worksheet name and range pretty quickly.

tell application "Microsoft Excel"
	tell worksheet "Name of Sheet" of active workbook
		copy range (range "A2:AB2")
	end tell
end tell
1 Like