2 Lists - Determining Where the Items Belong

Hi all,

I'm wondering if anyone has any input on the best way to do this.

I have two lists (these are tab delimitated variables in KM). List 1 contains the "Groups" and List 2 contains the "Items". Each item in each list has a number which corresponds to the placement of each in a theoretical 'combined' list. I need to figure out what "Group" each of the "Items" belongs to and then add that Group Name each Item in the Item List.

These two lists are generated dynamically with a separate Macro.

Here is the Group List

UI # GROUP
3 30 PIANO / KEYS MEL
6 35 PIANO / KEYS
9 40 MALLETS
27 64 GUITARS
31 70 BASS
59 115 PERC
67 161 DRUMS
86 234 FX

Here is the Item List

UI # TRACK LENGTH
4 31 Piano Sus 18
10 41 Prepared Piano - Played 42
19 50 Mallet 2
23 54 Mallet Rubber Soft 23
30 67 Guitar Lead 20
32 71 Bass Full 6
61 117 Hand Perc 14
68 162 Drumkit 35
103 253 Reverse 23

We only need to compare the # column. So for example, Track "Piano Sus" number is 31, which means it's part of the "PIANO / KEYS MEL" group (because the Group number is 30 and the track number is 31). Track "Hand Perc" # 117 belongs to the "PERC" group because 117 is greater than 115, but less than the next group DRUMS 161.

I would then add the Group name to end of the Item list, for each item, with an additional tab(\t).

The final list would look like this:

UI # TRACK LENGTH GROUP
4 31 Piano Sus 18 PIANO / KEYS MEL
10 41 Prepared Piano - Played 42 PIANO / KEYS
19 50 Mallet 2 MALLETS
23 54 Mallet Rubber Soft 23 MALLETS
30 67 Guitar Lead 20 GUITAR
32 71 Bass Full 6 BASS
61 117 Hand Perc 14 PERC
68 162 Drumkit 35 DRUMS
103 253 Reverse 23 FX

Thanks!

One way is:

  1. Sort the Group List by #.
  2. Use a For Each Line in the Item List loop to go through the Item list.
  3. Use a nested For Each Line in the Group List loop to evaluate If (Group List #) > (Item List #).
  4. If it is, then exit the inner loop, and append the current line of the Item List with a tab and the previous value of the Group column from the Group List.
1 Like

Thanks! Yes, this is what I started doing. But, I was starting with a For Each Line for the Group List and nesting the Item List. I just reversed that like you suggested and it's working now.

Thanks for that. Sometimes you just need someone to check your work, hah.

Excellent. Iā€™m glad it worked.