SOLVED

# Name Manager: Let and col formulas not encompassing all data

Brass Contributor

# Name Manager: Let and col formulas not encompassing all data

Hello!

I have the following formulas in my name manager, that are attached to this formula in G5:

=LET(col, XMATCH(E4,Names), CHOOSECOLS(Hours, col, col + 1))

I'm not sure what I am doing wrong, but it only will transpose the data from row G5:G321 and H5:H321. I need the data to pull all the info to G and H 325.

(It would be perfect if it would pull in all the data from column G to column N as well) :)

Thank you!

2 Replies
best response confirmed by kittenmeants (Brass Contributor)
Solution

# Re: Name Manager: Let and col formulas not encompassing all data

@kittenmeants If you're expecting the results to include rows 5 through 325, but it's only returning up to row 321, the most likely culprit is the formula definition for "Regions". COUNTA counts the number of cells in a range that are not empty. My guess would be that range E5:E325 contains 4 blank cells. TAKE(rng, COUNTA(rng)) will only return the number of rows that were counted as non-blank... if there are 4 blank cells somewhere within the data range, it will exclude the last 4 records.

As an alternative for the "Regions" definition, try the following:

``=LET(rng, BeautyMatrix!\$E\$5:\$E\$350, first, INDEX(rng, 1), first:XLOOKUP(TRUE, rng<>"", rng, first,, -1))``

As for your second inquiry about returning 8 columns instead of 2, try using the SEQUENCE function:

``=LET(col, XMATCH(E4, Names), CHOOSECOLS(Hours, col + SEQUENCE(, 8, 0)))``

It's difficult to grasp the full scope of what you're trying to accomplish without seeing all of your data. Hopefully this helps... Cheers!

# Re: Name Manager: Let and col formulas not encompassing all data

That worked perfectly. Thank you so much!
1 best response

Accepted Solutions
best response confirmed by kittenmeants (Brass Contributor)
Solution

# Re: Name Manager: Let and col formulas not encompassing all data

@kittenmeants If you're expecting the results to include rows 5 through 325, but it's only returning up to row 321, the most likely culprit is the formula definition for "Regions". COUNTA counts the number of cells in a range that are not empty. My guess would be that range E5:E325 contains 4 blank cells. TAKE(rng, COUNTA(rng)) will only return the number of rows that were counted as non-blank... if there are 4 blank cells somewhere within the data range, it will exclude the last 4 records.

As an alternative for the "Regions" definition, try the following:

``=LET(rng, BeautyMatrix!\$E\$5:\$E\$350, first, INDEX(rng, 1), first:XLOOKUP(TRUE, rng<>"", rng, first,, -1))``

As for your second inquiry about returning 8 columns instead of 2, try using the SEQUENCE function:

``=LET(col, XMATCH(E4, Names), CHOOSECOLS(Hours, col + SEQUENCE(, 8, 0)))``

It's difficult to grasp the full scope of what you're trying to accomplish without seeing all of your data. Hopefully this helps... Cheers!