Find and Return Next Two Rows (Batch Macro?)

[rjkim222]

Hello!

First time poster here. I have two datasets:

Dataset 1 - Server Listing:

Server Name

Server A

Server B

Server C

Server D

Dataset - 2Server Type and Version:

Generic Header

Server A

Server A Type

Server A Version

Additional unrelated records

Server A Version

Server B

Server B Type

Server B Version

Additional unrelated records

Server D

Server D Type

Server D Version

Additional unrelated records

Imagined Results:

Server Name	Type	Version
Server A	Server A Type	Server A Version
Server B	Server B Type	Server B Version
Server C	null	null
Server D	Server D Type	Server DVersion

I want each record (server name) from Dataset 1 to be searched against Dataset 2. If Server Name is found in Dataset 2, I'm wanting to return the server type and server version in the next two rows in Dataset 2 as two columns next to the Server Name in Dataset 1.

I've been looking at different batch macro examples as I think it would fit this scenario, but can't find any that would return the next two rows in a separate dataset. All records in Dataset 2 are in one column and there are additional records that do not need a return after the first two rows.

Thanks for any help or examples I can be pointed to for assistance!

[mattnason1]

Here's how I would do it (although I didn't set up as a macro).

[Luke_C · Accepted solution]

Hi @rjkim222 

No macro needed:

1. I used a multi-row formula to group the 2nd data set based on the empty row separating servers
2. Removed the empty rows and used a tile tool to number each field, then filtered to the first 3 rows for each server
3. Cross tabbed the data and joined to the server names
4. Unioned any fall outs to get your desired presentation.

[mattnason1]

Tweaked mine to get to output.  But looks like Luke made a nice solution too.

[rjkim222]

Thanks for spending time on my question. I'm seeing this as the final result when running the workflow:

I'm seeing where you were going with this one. Matching Server Names first, and then designating 1=true for the type and version if there was a match. What gets lost in the final output is the matching server name and then having them return as columns for each matched record.

[Luke_C]

@rjkim222 I believe my solution above should get you there. Please check and let me know.

[rjkim222]

Luke,

Thanks for taking your time for the write-up and the workflow. Got a couple useful things I can learn from this for future workflows in your expression and the use of the Tile Tool. Haven't seen that one used before.