Trying to categorize using XLOOKUP without spaces in between

[MitchMitchell]

I'm trying to organize data by first letter (not alphabetical). The function I'm using right now is IF(LEFT(Sheet1!A1,1) = "L", Sheet!A1,"") I know there has to be an easier way I just don't know what it is.
I'm new to the forum so I don't know how to attach an example. (I clicked attachments and it won't let me) So here's my best at an example

Column 1 Column 2
L5670 124
A550 235
A550 546
L9732 293
L2108 214
L3456 234
A0334 213
A3405 348
L3974 984

[wk9128]

@MitchMitchell follow method 2

Please read and refer to this link, how to upload attachments
https://www.excelforum.com/the-water...his-forum.html

[MitchMitchell]

Attachment 771513
Like this?

[wk9128]

The attachment download is invalid, please follow method 2

[MitchMitchell]

Did it work this time?

[TRICKYT57]

Your attachment does not seem to have uploaded. It is pretty complicated in my view and poorly explained, but you will probably get it if you carefully follow the above instructions.

In answer to your question, I agree that using XLOOKUP to sort data is quite cumbersome. To remove spaces you could use the search and replace function, highlighting the relevant data first.

As for sorting, have you tried using a table? Click on any cell in your data and then go to the Insert tab. Insert>Table Enter. Now you are all set to sort on any column by clicking on the downwards pointing arrow at the top of each column in your table.

[wk9128]

worksheet or Tab name : Sheet2

Cell A1 formula

Please Login or Register  to view this content.

Display three column

Please Login or Register  to view this content.

[Gregb11]

Do you want the spaces in between? If not, why not use FILTER?

=FILTER(Sheet1!A1:A32,LEFT(Sheet1!A1:A32,1)="L")

[TRICKYT57]

Here's your file back with a table inserted. Click on the down arrow in Cell A1, and sort alphabetically then un-tick "select all", and re-tick the ones you want starting with the letter "L".

[MitchMitchell]

I'm trying to get the data from one sheet to another, sorry for not clarifying. I don't think a table will work since I need to use data from the same sheet. I'm using an IF(LEFT function in order to get the data and then use an XLOOKUP in order to get the data from the other sheet. The problem I'm running into is that I don't believe the IF(LEFT function to be the most efficient way to get the data. Considering the spaces I get from each cell that doesn't have an L (see function from original post). I want to be able to get the data from one sheet and into another while skipping over all the other data that doesn't have an L. I believe that the document has been uploaded but may be starting on the second sheet. Thank you for trying to help tho it is greatly appreciated

[Gregb11]

Have you tried my formula in Post #8?

[MitchMitchell]

This worked on the sheet that I uploaded, however, when I tried to apply it to my larger excel sheet I got the #VALUE problem. On the other excel sheet the codes (L234) are in the middle would this make the formula not work? I'm going to attach an updated version to be more similar to my larger document.

[MitchMitchell]

It works when the codes (L6854) are on the left-most column however when I updated the worksheet to more accurately represent what I'm struggling with it doesn't work anymore

[Gregb11]

Well, the attached is NOT my formula from post 8, but yes, it makes a difference if you move the column around. You will have to change the references to Col A to Col C in the formula, like:
=FILTER(Sheet1!C1:C32,LEFT(Sheet1!C1:C32)="L")

[MitchMitchell]

It worked! Thank you so much and thanks to everyone who tried to help me. If I have more questions about my document, but it's not exactly related to this should I post a new thread or continue on this one?

[wk9128]

This worked on the sheet that I uploaded, however, when I tried to apply it to my larger excel sheet I got the #VALUE problem. On the other excel sheet the codes (L234) are in the middle would this make the formula not work? I'm going to attach an updated version to be more similar to my larger document.

Cell B1 formula

Please Login or Register  to view this content.

[Gregb11]

You're welcome. You would post a new thread.

[MitchMitchell]

Thanks, Where do I click to change this to solved?

[TRICKYT57]

Very simple, Just insert this formula anywhere, once, in juist one cell on your spreadsheet and it will list all the "L"s with no spaces:

Please Login or Register  to view this content.

[wk9128]

@MitchMitchell You're Welcome. Glad to help . Thank You for the feedback