• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating tables from tables.

W

Wulluby

Member
Hi,


Within a table I have 2 columns, one with text and another with numbers. An example being:

Text1 1

Text1 2

Text2 1

Text2 2

Text3 3

Text3 4


Is excel able to pull this into another table in another sheet like so:

1 Text1; Text2

2 #NA

3 Text3

4 #NA


The idea being that I am searching the text column, finding the minimum number a value such as Text1 has beside any of it's records from the number column then putting that value into the second table beside that number.


Thanks in advance.
 
Hi, Wulluby!

I don't understand the criteria for creating the new table.

In 2, shouldn't it be Text1; Text2?

Regards!
 
Hi Sir and thanks for the response.


The intention of the second table is to have the first column running from 1 down to whatever is desired, in this case 4. Both 1 and 2 sit beside a Text1 entry on the first table, the smallest number in the number column beside Text1 being 1 and it is this I am looking to try and translate into the second table. So if Text1 has 4 entries in the first table with 1,2,3,4 beside it, the second table will seek the lowest number and ignore everything above the lowest number.
 
Hi Wulluby,


I have worked out a file, see and give feedback. :)


http://www.2shared.com/file/T-XbWo4R/Copy_of_Table_in_Tables.html


Regards,

FASEEH
 
Hi Faseeh,


Thanks for putting the time into that. It loooks exactly like the kind of end result I was going for. I imagined the number column 1,2,3,4... to be entered manually but I see you've managed to have that working automaticaly too :)


I will have a look at that over the weekend to see if I can get the same results.


I noticed that it has curly brackets which I'm not quite up to speed on yet, does that mean I need to use CTR+SHT+ENTER? I take it I can still click and drag that formula to copy it over a region and also able to pull the data from another sheet. I will have to read up on those brackets, and arrays. I'm noticing I'm not to strong on those.


Thanks again Faseeh, that looks spot on.
 
Hi Wulluby,


Nice to read your feedback. :) Curly braces are used for array formulas and have to be executed with Ctrl+Shift+Enter, otherwise they don't behave as same.


Regards,

FASEEH
 
Hi Faseeh,


Sorry for the delay, I had a closer look at that last night. It's not quite doing what I had in mind although at a glance it looks right. The first column on the second table should always run down from 1,2,3... but I noticed that that column on your tables, (D I think it was but being on a box using 2k3 I am unable to double check), the numbers seem to run with what ever comes first from B is it?


The first set of letters, SS, both have the number 1 sitting beside them on the first table but if I change this to 21 for example then that first value of the second table changed to 21.


So whereas that 1st column of the 2nd table should always remain fixed from 1, 2, 3 onwards its the columns to the right that will change depending on the info from your first table. I forget which one was a good example. Something that had 2 entries with 2 different numbers beside it, let's say DE as an example. If DE had 1 in one entry and 5 in another then the 5 would be ignored and the DE text would be entered beside the 1 in the second table as it is the lowest number.


Let's also say that nothing in the first table had a 6 in the second column, that 6 would still exist in the second table but with no entries to it's right.


Let me know if that doesn't make sense.


W
 
Hi Wulluby,


Welcome back!!


What i can understand from your entire post is that you are finding the column C of the table "problematic" because that automatically takes up values from column B?? Is it so?


Let me explain purpose of each column:


1. Column A & B Occupies your original data what so ever it is.

2. Column C was designed to extract "unique values" from column B.

3. Column D to L has the resultant data that you needed.


If you want to have a continuous no. in you Column C as you have refereed to the 2nd table in your initial post , then simply enter 1 in cell C3 (in my sheet) and drag it down to complete the series to your requirement. The formula that finds out unique value from the column B will be replaced by a series of nos running from 1 to n.


I have changed and uploaded the file here see and give feedback!!

http://www.4shared.com/file/2w1IcddG/Copy_of_Table_in_Tables_2.html


Regards

Faseeh
 
Hi Faseeh and thanks for taking the time.


That looks closer but there are duplicates on the second table. Take DD as an example, all that the second table is looking for is the lowest number by DD on the first table, so in this case you should see DD beside the 4 on the second column then all DDs with a value higher than that are ignored. So it should be just one representation of each text cell in the second table and that would be beside the lowest number that sits beside it on the first table. If that can be done?


W
 
Hi Wulluby,


This time i am unable to understand your post, do one favor. Download the file, Change the area from Col D to L as per your requirement (i.e. remove formulas and enter values manually) so that i can understand what is you exact requirement and either give me upload link or mail me at faseeh10@hotmail.com


Thanks

Faseeh
 
Hi Faseeh,


Will do, I forgot to save as an xls to look at in work and will be out tonight but will email you over the weekend.


Thanks.


Wull
 
You must log in or register to reply here.
Back
Top