Tech Support Guy banner
Status
Not open for further replies.
1 - 14 of 14 Posts

·
Registered
Joined
·
378 Posts
Discussion Starter · #1 ·
Hi,

I have an urgent problem: I have these datas:
Each list is different and doesn't have all the letter symbols and not in the same order.

01_f
BC 12 70.59%
BI 2 11.76%
X 2 11.76%
D 1 5.88%
Total 17

01_m
BC 24 96.00%
D 1 4.00%
Total 25

02_f
BC 8 50.00%
D 3 18.75%
X 3 18.75%
AC 1 6.25%
AN 1 6.25%
Total 16

etc...
(there are hundreds of lists like that all differents)

then I have this list of letter symbols that I have to fill in, the full list:

BC
AC
CC
BI
LI
FI
AN
AA
X
U
D
T

I need, for each list, to look for the % in front of one letter symbol and put it in front of the same letter symbol in the full list.
For example, from list one I need to take the % in front of BC (70.59%) and copy it in front of BC in the full list.
Then do it again with BI, X etc. Then do it again with the next lists...

to have the following table (imagine that _____ are empty cells):

BC_____70.59%______96.00% 50.00% 60.00%
AC_____ _____ _____ _____6.25% 4.00%
CC______ _____ _____ _____ _____ _____
BI _____11.76%______ _____ _____ _____
LI ______ _____ _____ _____ _____ _____
FI ______ _____ _____ _____ _____ 4.00%
AN______ _____ _____ _____ 6.25% _____
AA______ _____ _____ _____ _____ _____
X ______11.76%_____ _____18.75% 12.00%
U ______ _____ _____ _____ _____ _____
D _______5.88% 4.00% ______18.75% 20.00%

Is there any formula to do this? :confused:
 

·
Registered
Joined
·
377 Posts
It might be easiest to use Text To Columns on your data then a simple vlookup formula could be used to fill your table
 

·
Registered
Joined
·
743 Posts
Where are all these " hundreds of list" currently? Already in EXCEL or are you going to be entering the data.

If they are already there - where are they? All on one sheet or everyone in a separate sheet?

It makes a big difference.


- C
 

·
Registered
Joined
·
378 Posts
Discussion Starter · #4 ·
These are the list of raw datas:

_ 01_f
BC _ 12_ 70.59%
BI _ 2_ 11.76%
X _ 2_ 11.76%
D _ 1_ 5.88%
Total _ 17 _
_ _ _ _
_ _ _ _
_ 01_m
BC _ 24_ 96.00%
D _ 1_ 4.00%
Total _ 25
_ _ _ _
_ 02_f
BC _ 8_ 50.00%
D _ 3_ 18.75%
X _ 3_ 18.75%
AC _ 1_ 6.25%
AN _ 1_ 6.25%
Total _ 16_
_ _ _ _
_ _ _ _

_ represent cell separators.
For each excell document I have between 2 and 10 of these raw lists.
Actualy these raw lists are on a signle column but I can make them appear on diferrent column if needed.

I have to enter manualy in each excell document, sometimes 3, sometimes 5, sometimes 4 of these raw lists...

For each document I have to make one list of letter codes with the percentages regrouping all percentage of the raw lists in this document with one column for each raw list.
If a letter code is not present in the raw list, then number is zero or an empty cell.

This is the list for the result:

AC
CC
BI
LI
FI
AN
AA
X
U
D
T

and this what I need.

BC_ 70.59% _ 96.00% _ 50.00%
AC_ _ _ _ 6.25%
CC_ _ _ _
BI_ 11.76% _ _ _
LI_ _ _ _
FI_ _ _ _
AN_ _ _ _ 6.25%
AA_ _ _ _
X_ 11.76% _ _ _ 18.75%
U_ _ _ _
D_ 5.88% _ _ _ 18.75%
T_ _ 4.00% _ _

This doesn't render very well visualy but if you copy this message to Word, replace _ by tab and re-copy it to Excell, you will see better what I mean...

or look at this:
BC_____70.59%______96.00% 50.00% 60.00%
AC_____ _____ _____ _____6.25% 4.00%
CC______ _____ _____ _____ _____ _____
BI _____11.76%______ _____ _____ _____
LI ______ _____ _____ _____ _____ _____
FI ______ _____ _____ _____ _____ 4.00%
AN______ _____ _____ _____ 6.25% _____
AA______ _____ _____ _____ _____ _____
X ______11.76%_____ _____18.75% 12.00%
U ______ _____ _____ _____ _____ _____
D _______5.88% 4.00% ______18.75% 20.00%

The goal is to create a chart with the percentages of each lettercode, each raw list being a serie.

Thanks for helping!!
 

·
Registered
Joined
·
743 Posts
Since you said the raw data was all in one column I tried the following:

I assume for example that each raw list can have up to 10 parameters like AC or D etc - and allotted each raw list 10 rows (with some blank rows separating the lists)

If you can enter the raw data like this then it should work.
For my example I set up the following:

RAW LIST 1 had its info in rows 1 - 10
RAW LIST 2 had its info in rows 20 - 30
RAW LIST 3 had its info in rows 40 - 50
and on and on

Then I went over to COLUMN F and listed all the categories vertically - AA AB AC

F1 containing AA
F2 containing AB
F3 containing AC ... and so on

Column G had these formulas :
=VLOOKUP( $F1,$A$1:$D$10,3,FALSE) in G1
=VLOOKUP( $F2,$A$1:$D$10,3,FALSE) in G2
=VLOOKUP( $F3,$A$1:$D$10,3,FALSE) in G3
etc...
(3 being the number of columns over where it retreived it's data)

Column G was only for the only checking raw data in the first 10 rows corresponding to RAW LIST 1

Column H was again looking p the headings AA, AB, AC, etc in column F but only checking in rows 20 to 30 corresponding to where RAW LIST 2 was resident

it had formulas like:

=VLOOKUP( $F2,$A$20:$D$30,3,FALSE)

The end result was that the desired data from RAW LIST 1 was listed vertically in COLUMN G
RAW LIST 2 in COLUMN H
RAW LIST 3 in COLUMN I

and on and on

If it is convenient for you to input the RAW LISTs in this manner - defined to certain rows - this should work fine.
You can use IF formulas perhaps to remove all #N/A s

-Castleheart
 

·
Registered
Joined
·
378 Posts
Discussion Starter · #7 ·
In fact the raw lists I was talking about are the result of a count of how many times a lettercode (BC, AN, D...) appear in a raw-raw list:

BC
BC
BC
BC
BC
empty cell
empty cell
E13
E2
E4
E
BC
E2
E2
E2
BC
BC
BC
BC
D
BC
BC
BC
BC
BC
E2/E3
EB
D
BC/E2 (here BC and E2 are in the same cell but must be counted as separate symbols)
BC
BC
BC
BC
BC
... list goes on 100+ rows down

Then have the numbers in % or in digit dispatched in the result list like this:

BC_____70.59%______96.00% 50.00% 60.00%
AC_____ _____ _____ _____6.25% 4.00%
CC______ _____ _____ _____ _____ _____
BI _____11.76%______ _____ _____ _____
LI ______ _____ _____ _____ _____ _____
FI ______ _____ _____ _____ _____ 4.00%
AN______ _____ _____ _____ 6.25% _____
AA______ _____ _____ _____ _____ _____
X ______11.76%_____ _____18.75% 12.00%
U ______ _____ _____ _____ _____ _____
D _______5.88% 4.00% ______18.75% 20.00%

It's not a problem to auotmaticaly count them but if you had a ready formula to do this, it would be even better.

In any case many thanks for your help!
 

·
Registered
Joined
·
743 Posts
:eek: Oh NO !!!!

You have a list that is rawer than the raw list!!!!!


Seems to me it's all going to be a matter of how you choose to best organize it for your own needs.

Your COUNT and COUNTA formulas look like they would work best before the final tally page - whether in the RAWRAW lists or in a separate sheets that then becomes the RAW lists.

HOWEVER -

If this data entry was fairly standard - and you entered the latest RAWRAW data in a worksheet - you could include your COUNT formulas within these sheets in a standardized cell location for each RAW LIST/Worksheet -

then your TALLY page could be set up simply to go retreive that data from those set cells and you could skip the LOOKUP. Is that confusing? :rolleyes:

example:

10 different RAW LIST worksheets - all the letter code data input listed in column A - and then the itemized codes COUNTED and PERCENTILED in cell block B1:Z2 using COUNTA, SUM, and %

Your COMPILATION worksheet would then mererly be copying the results of those standardized cells from those ten worksheets.

In the AB row -
COLUMN A would have the code AB
COLUMN B would have the results for % AB from the first RAW LIST.
COLUMN C - the results for % AB from the next RAW LIST and so on.

You raw lists could be 10 rows or 1000 rows - it wouldn't matter - and they could also be different rows of the same worksheet if you needed it to be that way - so long as you kept your COUNTA formulas in a standard location within those lists for retreival by the COMPILATION sheet

Let me know what you work out!

- C

(Personally I think you need to change to a RAW list - MEDIUM list - & WELL DONE list !):rolleyes:
 

·
Registered
Joined
·
378 Posts
Discussion Starter · #9 ·
Don't worry there won't be another lower level of raw list! :D

I will try all these advices. yesterday I wanted to try the vlookup formula but it didn't work, I think because of the way my data was stored in txt, before.

I got a small macro from someone else. It does the job (the 1st I asked for), look similar to the vlookup function.

But I will try yours as well because it will be better to start from the raw datas and not from a medium list.

again many thanks for your help.
 

·
Registered
Joined
·
378 Posts
Discussion Starter · #10 ·
I tried it and it works but...copy-paste the formula doesn't work.

I don't why. In you example there is a space at the begining of the formula, but removing this space doesn't fix it.
I had to recreate the formula by using the formula bar. I got the excately same formula, working.

Thanks a lot! Now my tables are done in a milisecond!
 

·
Registered
Joined
·
11,755 Posts
Ah...CastleHeart...
What's uuuuuuuuuuuup??

I see you are just taking over here. That's terrific!

:D

Good posts!
Let me know next time you're in town.
I'll take you out for a hoagie and a tastykake.
 

·
Registered
Joined
·
743 Posts
ONLY if you promise not to have any of this damn heat and humidity!!!!!!

Our heat index down here has been over 110 for days!
You don't want to ride in the boat - but jump overboard and swim beside it !

I'm melting! I'm melting! :(

I hear tell, it's cooler in them thar hills !

- Castleheart
 
1 - 14 of 14 Posts
Status
Not open for further replies.
Top