New Posts  All Forums:Forum Nav:

Excel Help

post #1 of 9
Thread Starter 
OK so here is my situation...i am creating a database in mySQL and its going to hold all my music in my itunes and each artist is going to have a unique ID as well as each song and each album
i can do it with the songs easily because there are no duplicate songs...but with artists if i drag the number all the way down i get like X amount of IDs because each artist has X amount of songs
so i want it to look like this
I entered all these in manually..now that wouldnt be a problem but there are over 1000+ artist that i need to have an ID for

this is what i get when i try to do it automatically..see how there are multiple IDs for the same artist
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
post #2 of 9
Did you make a mistake with your ids? I see 50 Cent listed as both 2 and 3, and so on down the line, like the numbers are one off.

So you just want the ID number to change whenever the Artist column changes?

edit:
If that is the case then I would make sure you have every Artist name exactly the same, since any extra spaces or different spelling would cause problems. Then sort by the Artist column to get them in order.

Next type the number 1 in cell E2, which should be your first ID. Then type this into cell E3
=IF(D3=D2,E2,E2+1)

Highlight E3 and copy the formula all the way down the list – shortcut to copy down is double click the lower right hand corner square around the highlighted box.

I think that is what you want, if so you now have a dynamic cell formula in the ID column. You can leave it that way, but it might be better to highlight the whole column, right click, copy, then right click and paste special values. That will replace the formula with the actual numbers.

If that is not what you need, just let me know, I am sure we can figure it out.
Edited by justanoldman - 5/29/13 at 9:03am
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
post #3 of 9
Thread Starter 
you are a live save man thank you if i could would give you more than 1 rep smile.gif
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
post #4 of 9
One is plenty, thanks for that.
Glad it worked out.smile.gif
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
post #5 of 9
Thread Starter 
ok now i have one more excel problem

now i want to add all the times of each song to make a total time for each album..how would i go about doing that?
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
post #6 of 9
It looks like you used the same thing in Column F to give a number to each album, just like you did for each artist. If so that is perfect since we need a unique albumn number to work with.

Now just use the unique album numbers in F to do something similar as before. There is a sumif function that lets you set conditions.

You need to know your total range of data for this. For the formula listed below I will assume your unique album id is in column F and the song times are in B, and you have data all the way down to row 1005 (just a made up number you need to fill in the last row number of your data). Replace the two 1005 numbers below with the real last row number.

In cell G2 (I assume G1 is a title like Album Time) you want:
=SUMIF($F$2:$F$1005,F2,$B$2:$B$1005)

Then same procedure, copy it down, then you can copy and paste special values for the whole column.

I think that works and is what you want, let me know if not.
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
post #7 of 9
Thread Starter 
ok thank you ill try that in a lil bit and let you know
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
post #8 of 9
Thread Starter 
Thank you again that worked perfectly
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
The Prodigy
(16 items)
 
 
Dream Rig Contest
(11 items)
 
CPUMotherboardGraphicsRAM
[AMD] Athlon II x2 255 [Gigabyte] GA-78LMT-SP2 [Onboard] ATI Radeon 3000 [Crucial] Ballistix 8GB (2x4GB) 
Hard DriveHard DriveHard DriveOptical Drive
[Western Digital] Blue 320GB [Maxtor] 320GB soon to be adding 4TB from WD Red [Lite-On] DVD Player/Burner 
CoolingOS
[Stock] [Mircrosoft] Windows 7/ Home server 2011 
  hide details  
Reply
post #9 of 9
smile.gif
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
Ivy 5.0
(14 items)
 
Ivy 4.8
(15 items)
 
 
CPUMotherboardGraphicsRAM
i7-3770k 5.0 24/7 Maximus V Formula EVGA GTX 690 G.Skill Trident X 2400 (2x8gb) 
Hard DriveOptical DriveCoolingOS
Samsung 840 Pro 512GB Pioneer BD-RW Swiftech H220 Windows 7 Ultimate 64 bit 
MonitorKeyboardPowerCase
2 x NEC 30" Logitech G15 Corsair AX1200 NZXT Switch 810 
MouseAudio
Logitech G9x Logitech G51 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Other Software